Hi all, I have built a PivotTable with Data Model and defined my Sales Value as:
=[Sum of Sales Qty]*[Sum of Selling Price] with DAX formula.
[Grand Total of Sales Value] should be [Sum of Sales Value], instead it is showing [Sum of Sales Qty]*[Sum of Selling Price.]. How do I correct it?
Please help! Thank you!
Joseph
Hi,
The current total is perfectly correct, in the sense that it is consistently applying the logic of the measure to the total row.
To override this default behaviour, you could use:
Sales Value:=IF(HASONEVALUE(ProdSales[Product]),[Sum of Sales Qty]*[Sum of Selling Price],SUMX(ProdSales,ProdSales[Sales Qty]*[Sum of Selling Price]))
Regards
Thank you, XOR LX! Is there anyway to also not show the Grand Total for Sum of Selling Price?
DAX Formula in PivotTable gives wrong Total Sales Value.png
You mean you just want it to be blank instead?
Regards
That's right.
You'll have to create a new measure and include that in the pivot:
New Selling Price:=IF(HASONEVALUE(ProdMaster[Selling Price]),[Sum of Selling Price],BLANK())
Regards
Thanks XOR LX, work perfectly! Rep added!
You're welcome!
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks