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

Aruns65

New member
Joined
Dec 13, 2015
Messages
2
Reaction score
0
Points
0
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
 
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.
 
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.
 

Attachments

  • SLOB.zip
    461.4 KB · Views: 13
Back
Top