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
 

Herbds7

Banned
Joined
May 8, 2013
Messages
197
Reaction score
0
Points
0
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.
 

Aruns65

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