DAX Calculate Column results in Wrong Table Total

[Blevels]

Hi,

A Visualization Table Total value for a calculated measure is incorrect.

I have a table visualization that includes a list of inventory parts and the part attributes.

This Table has two summary fields [COST] and [QUANTITY].  

A measure called [VALUE] is defined AS:  Value = CALCULATE(SUM('TABLE'[Cost]) * SUM('TABLE'[Quantity]))

This measure is included in the Columns on the Table Visualization and is displayed correctly for each row.

However, turning on the Totals results in a very wild value (trillions) for the [VALUE] measure.

Should this calculation be moved back to the Table creation? What is the best way to obtain the correct table total? 

In Advance, thank you for your input to this solution.

[v-zhangti]

Hi, @Blevels 

You can try the following methods.

Value = CALCULATE(SUM('Table'[COST])*SUM('Table'[QUANTITY]))

Measure = IF(HASONEVALUE('Table'[Part]),[Value],SUMX('Table',[Value]))

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM · Accepted solution]

Hi @Blevels 

First, change your measure to this:

Value =
SUMX ( 'TABLE', 'TABLE'[Cost] * 'TABLE'[Quantity] )

And read this blog post to find out how to manage this situation:
https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube