Forum Discussion
Problem with the grand total when using FIRSTNONBLANK function
Hi,
I'm having a fact table, where from several reasons there are stored repeated numeric values in one column. I need each value only once, so instead of using SUM('Fact Sales Order'[Quantity]), I'm using a workaround FIRSTNONBLANK('Fact Sales Order'[Quantity], 1). It works perfect for me, but the problem with this solution is that when I'm trying to create a pivot table in excel, the grand total appears to be zero, even though the values inside the table are correct and there are no zeros.
Is there a way to fix this, or maybe there is an alternative function that I can use for such purposes?
I would appreciate any advice
Thanks in advance!
Hi Anonymous,
Try this instead:
MEASURE = SUMX(VALUES('Fact Sales Order'[Quantity]),'Fact Sales Order'[Quantity])
4 Replies
Hi Anonymous,
Try this instead:
MEASURE = SUMX(VALUES('Fact Sales Order'[Quantity]),'Fact Sales Order'[Quantity])Hi Payeras_BI,
I'd like to ask you, if you could help me with one problem related to the issue above. I'm making snapshots of Sales Order Quantity on specific dates. I found out, that the quantities don't sum up through the Snapshot Date dimension:
On these two dates quantities were the same, which is right, but the Grand total is wrong.
I would appreciate any advice
Thanks in advance! And thank you one more time for helpnig me out with the previous issue!
Hi Anonymous,
The solution could be again on iterating those dates and then adding up. A variation of the formula already provided would do it.
In case you wanted further help, please provide enough details to reproduce your scenario.