DAX Measure, Total is wrong

[LuITS]

Hello I have problems converting formulas into DAX measure,

Measure 1:
SUM(Quantity) / SUM( Quantitiy / Goal )

My try:

DIVIDE('Table'[Quantity], SUMX('Table', DIVIDE('Table'[Quantity], 'Table'[Goal])))

Measure 2:
Other Measure / Measure 1

My try:

DIVIDE('Table'[Other Measure], 'Table'[Measure 1])

Problem is, that Calculations in Table Visual are right, but Total of Measure 2 is not correct. Measure 2 is displayed in %.

Output:

Quantity	Goal	Measure 1	Other Measure	Measure 2
158	2.400	2.400	4.277	178,2 %
1.734	3.300	3.300	2.857	86,6 %
564	2.650	2.650	2.093	79,0 %
130	2.400	2.400	1.910	79,6 %
Total                       2.586			2.635	88,4%

All values are correct, but total of Measure 2 is off and should be 87,5%

can u help?

[rohit1991]

Corrected DAX:

1. Measure 1: Measure 1 = DIVIDE(SUM('Table'[Quantity]), SUMX('Table', DIVIDE('Table'[Quantity], 'Table'[Goal]))

2. Measure 2 (Fix Total): Use SUMX to correctly calculate the total:
   Measure 2 = VAR TotalQuantity = SUM('Table'[Quantity]) VAR TotalOtherMeasure = SUM('Table'[Other Measure]) VAR TotalMeasure1 = DIVIDE(TotalQuantity, SUMX('Table', DIVIDE('Table'[Quantity], 'Table'[Goal])))
   RETURN DIVIDE(TotalOtherMeasure, TotalMeasure1)
3. This ensures the total recalculates correctly as 87.5%.

for further reference please refer -->>https://www.sqlbi.com/articles/why-power-bi-totals-might-seem-inaccurate/

[LuITS]

Hi, i actually had an error in my data. Thanks for your help anyways.

[FarhanJeelani]

The issue with the incorrect total for your Measure 2 lies in how Power BI calculates totals for measures. By default, Power BI aggregates the row-level results, which can lead to discrepancies when the logic of the measure depends on row-level context. Instead, you need to adjust the logic of the measure to calculate the total correctly.

For Measure 2, instead of summing the row-level results, we should calculate the overall total using the same logic applied at the row level.

Measure 1:

DAX

Measure 1 =

DIVIDE( SUM('Table'[Quantity]), SUMX('Table', DIVIDE('Table'[Quantity], 'Table'[Goal])) )

Measure 2 (Adjusted for Total):

DAX

Measure 2 =

DIVIDE( SUM('Table'[Other Measure]), SUMX('Table', DIVIDE('Table'[Quantity], 'Table'[Goal])) )

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

[LuITS]

Hi, thanks for youre reply but i get error.

When i try to use Measure 2 i get following Error:
Collumn Other Meassure not found or cant be used in this expression.

DAX of Other Meassure: DIVIDE(SUM('Table'[Quantity], SUM('Table'[Time])) * 60

[FarhanJeelani]

Use below:

 Other Measure:

Other Measure = DIVIDE(SUM('Table'[Quantity]) + SUM('Table'[Time]), 1) * 60

Measure 2:

 Measure 2 = DIVIDE(

[Other Measure], SUMX('Table', DIVIDE('Table'[Quantity], 'Table'[Goal])) )

[LuITS · Accepted solution]

Hi, i actually had an error in my data. Thanks for your help anyways.