Forum Discussion

LuITS's avatar
LuITS
Helper II
1 year ago
Solved

DAX Measure, Total is wrong

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:

QuantityGoalMeasure 1Other MeasureMeasure 2
1582.4002.4004.277178,2 %
1.7343.3003.3002.85786,6 %
5642.6502.6502.09379,0 %
1302.4002.4001.91079,6 %
Total                       2.586  2.63588,4%

 

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

 

can u help?

@Measures
  • LuITS's avatar
    LuITS
    1 year ago

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

6 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

     

    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's avatar
      LuITS
      Helper II
      1 year ago

      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's avatar
        FarhanJeelani
        Super User
        1 year ago

        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])) )

         

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    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%.

     

    • LuITS's avatar
      LuITS
      Helper II
      1 year ago

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