Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

PowerBI Wrong Total Sum

Hi, PowerBI Community,

I have a problem with this. I have measures like this: ppu_ac, ppu_ly. I use this in "Matrix" visual. For the calculation, I used this formula, simple division with DIVIDE:
ppu_ac = DIVIDE([AC Revenue], ([Ac_hl])). I have about 15 such formulas.

 

But the total sum doesn't add up:

What should I use? This didn't work for me either:

Measure = 
IF(
    HASONEVALUE('Tabulka'[Dimenze]),
    [Vzorec pro řádek],
    [Vzorec pro total]
)

Thank you so much. 

  • Shahid12523's avatar
    Shahid12523
    1 year ago

     Problem: DIVIDE([AC Revenue], [Ac_hl]) works per row, but totals in Matrix aggregate first, then divide — causing wrong results.
     Fix: Use SUMX to force row-wise calculation even in totals:


    ppu_ac_fixed =
    SUMX(
    VALUES('Tabulka'[Dimenze]),
    DIVIDE([AC Revenue], [Ac_hl])
    )


    Optional: Wrap with HASONEVALUE if you want different logic for rows vs totals.

  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    Hi Anonymous 

     

    For the total alone try using SUMX:

    ppu_ac_custom = 
IF(
    HASONEVALUE('Tabulka'[Dimenze]),
    DIVIDE([AC Revenue], [Ac_hl]),               
    SUMX(
        VALUES('Tabulka'[Dimenze]),
        DIVIDE([AC Revenue], [Ac_hl])
    )                                          
)

4 Replies

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous 

     

    For the total alone try using SUMX:

    ppu_ac_custom = 
IF(
    HASONEVALUE('Tabulka'[Dimenze]),
    DIVIDE([AC Revenue], [Ac_hl]),               
    SUMX(
        VALUES('Tabulka'[Dimenze]),
        DIVIDE([AC Revenue], [Ac_hl])
    )                                          
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you, it works :)) 

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

     Problem: DIVIDE([AC Revenue], [Ac_hl]) works per row, but totals in Matrix aggregate first, then divide — causing wrong results.
     Fix: Use SUMX to force row-wise calculation even in totals:


    ppu_ac_fixed =
    SUMX(
    VALUES('Tabulka'[Dimenze]),
    DIVIDE([AC Revenue], [Ac_hl])
    )


    Optional: Wrap with HASONEVALUE if you want different logic for rows vs totals.

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    1 year ago

    What result are you looking for?

     

    Once you clarify this, in order to help I need to see the visual in a larger way, can you attach a picture that shows the visual and the grouped columns (the columns you grouped in the rows section of the visual)?

     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI