Results 1 to 4 of 4

Thread: How to sum an IF-AND function in powerpivot...

  1. #1

    How to sum an IF-AND function in powerpivot...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I was trying to apply a formula to calculate the Slowmoving stocks in powerpivot datatable. Below is the formula that I used in the calculated fields area.

    OBS_2M:=IF(AND([2M_Qty]=0,[Inv_Manual]>0),[Inv_Manual],0)

    While checking the powerpivot file, I noticed that the column called OBS_2M, reflects a grand total as '0', at the bottom of the table. while if I scroll across the OBS_2M column, there is a value!

    Can you please advise what could be the possible reasons for not seeing a grand total value.


    Regards,
    Arun

  2. #2
    The Total operates on the numbers that are shown in its row,
    not the numbers in its column,
    with the formula you have shown.
    Use HasOneValue() and a different formula to show sum.
    Share file if stuck.

  3. #3

    File attached...

    Hi Herbds7,

    I have attached the file. Please refer:

    In data for powerpivot, BOM_Expansion sheet. You can see in the calculated fields SLOW_2M and OBS_2M.
    When I view the pivot report, the grand total for SLOW_2M does not tally if I manually highlight the column and check the totals.
    For OBS as well, there is a value, however it does not reflect in the Grand total. The formula that I applied for Slow and OBS seems to be fine if I check cell by cell.

    Please advise solution.
    Attached Files Attached Files

  4. #4
    Re-read my previous post and any book on PowerPivot.
    http://www.mediafire.com/download/jci2evgam024jaz/12_14_15.xlsx


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •