Forum Discussion
Dax - Multiplying non-aggregate
I need to make a calculation that takes Unit Price * Quantity.
factSales has the Quantity field. dimOrder has the UnitPrice field. There is a *:1 relationship setup between them based on OrderNumber,LineNumber,RelNumber.
I created a measure in Power BI that takes SUM(dimOrder[UnitPrice])*SUM(factSales[Quantity])
The results on the lines in my table look good. However, the Total at the bottom seems to take the summed up amount of Unit Price * summed up amount of Quantity, and that's not correct.
So my individual lines in the table sum up to $3.5M (exported data to excel and manually summed them), but the Power BI Grand total is showing $10M
how can i get this to show the $3.5M real total at the bottom? I tried changing the Unit Price summarization to Don't Summarize, but that didn't help.
Hi Anonymous
How about you bring the price into the Sales table (possibly with RELATED( )) and then try this measure:
Measure = SUMX(factSales, factSales[UnitPrice]*factSales[Quantity])
Otherwise, share the pbix to see what other options there are.
1 Reply
Hi Anonymous
How about you bring the price into the Sales table (possibly with RELATED( )) and then try this measure:
Measure = SUMX(factSales, factSales[UnitPrice]*factSales[Quantity])
Otherwise, share the pbix to see what other options there are.