Hi,
I’m trying to create a DAX multiplication function. However, The results do not always match expected. See example below.
Can someone please help me with the DAX and concept?
Extended Cost = vw_abc[Qty]*vw_abc[Unit Cost]
Many thanks!
Julie Bunavicz
This isn’t much to go on.
From the information provided, it’s not entirely clear whether you’re attempting to perform row-by-row multiplication or an aggregated multiplication across the entire dataset. The discrepancy in your results could be due to the filter context, which can affect calculations in Power BI depending on how your report is structured and what filters or slicers are applied.
To perform row-by-row multiplication and create a new column that calculates the extended cost for each individual row in your table, you would simply multiply the two columns directly, as you’ve shown. This could be done in a calculated column, like so:
Extended Cost = vw_abc[Qty] * vw_abc[Unit Cost]
However, if you’re trying to create a measure that aggregates over the entire table or within the current filter context, then wrapping the columns in a SUM function is appropriate. This would give you the total extended cost for the entire dataset or within the current filter context, but not on a row-by-row basis:
Total Extended Cost = SUM(vw_abc[Qty]) * SUM(vw_abc[Unit Cost])
Let us know if you’re looking for an aggregated total or a detailed row-by-row calculation. Also, it would be helpful to understand the filter context in which this measure is being used, as that could influence the result.
Can you share a .pbix file?
Okay. Good luck.
HI there,
You might want to check out this video that might explain the totals. Its a know issue
Dealing with Incorrect Totals in Power BI (youtube.com)
thanks
Keith
Have a look at working through a solution here in data mentor
To me you don’t need the CALCULATE
Another detailed exploration into the formulas here
Hi @jbunavicz - Can you try something like below.
Extended Cost = if(HASONEVALUE(Test[Unit Cost]),max(Test[Unit Cost]) * max(Test[Qty]),Sumx(test,Test[Unit Cost] * Test[Qty]))
Thanks
Ankit J