Totals not displaying properly in Power BI Desktop: Table visualization

[Anonymous]

I have a measure which uses formula to compute Revenue change price $. The individual rows line by line are all correct, except that the "Total" value is incorrect.

I tried creating a calculated column instead of a measure but keep getting loop issues in the formula. Is there a simple way to have the Revenue Price change $ column just sum the records instead of how it is calculated now?

Should I contine using a calculated measure or should use a calculated column rather? Is there an easier/right way to display the totals correctly in Power BI?Screenshot from excel header displaying Column K formula and sample data. Thanks!

Expected result: $ -61,508.2

Current result: $ -76,814,641.80

1. Calculations/DAX formula below:

Revenue change Price $ = [Price $ variance] * [PY Total Volume]

Price $ variance = [CY Total Price $] - [PY Total Price $]

2. Also attached excel screenshot for reference where the total (sum) is displayed as -61,508 for column K (Revenue change price $).Revenue change price change $ total is not displaying correctlySum for Column K is -61,508. This is the expected total value in Power BI.

[mark_endicott]

@Anonymous - try, this will force the calculation to iterate through the rows of your table and perform the calcualtion, at them moment it is not doing that at a total level. 

SUMX( VALUES(Table[dcitemnumber], [Price $ variance] * [PY Total Volume])

Just replace the word Table, with the name of the table your column lives in. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[Anonymous]

Thanks, but it still doesn't work. 

SUMX values takes only a table or a column - single parameter as an argument. What you have given below is an expression and so I get the error in DAX that too many arguments used.

[Price $ variance] * [PY Total Volume])

So, do I have to create a new calculated column first and include that calculated column in the SUMX Values DAX? I had trouble creating new calculated column instead of a measure because of loop issues. I need to calculate the price variance first (current year price minus last year price), and multiply that with the PY Total volume. If you can provide me with a DAX for the calculated column, that would be great!

I can then try with SUMX values for this calculated column and see if that brings the correct total. Thanks!

[mark_endicott · Accepted solution]

@Anonymous - My bad, I missed a bracket. try this:

SUMX( VALUES( Table[dcitemnumber] ), [Price $ variance] * [PY Total Volume])

And to be clear, do this in a measure. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[Anonymous]

Azmazing, this worked!! thanks much, also accepted as the solution 🙂