PDA

View Full Version : How to sum an IF-AND function in powerpivot...



Aruns65
2015-12-13, 11:19 AM
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
2015-12-13, 09:51 PM
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
2015-12-14, 07:33 AM
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.

Herbds7
2015-12-14, 05:23 PM
Re-read my previous post and any book on PowerPivot.
http://www.mediafire.com/download/jci2evgam024jaz/12_14_15.xlsx

(http://www.mediafire.com/download/jci2evgam024jaz/12_14_15.xlsx)