Forum Discussion

yzornetta's avatar
yzornetta
Regular Visitor
1 year ago
Solved

Incorrect Totals even with SUMX!

Hi All!

Like many others, I'm experiencing issues with Totals in a Matrix. Despite trying various solutions involving SUMX and SUMMARIZE, I couldn’t solve it so far.

I've attached a file with sample data for your reference.

 

 

To calculate Total Revenue, I need to multiply a fixed rate (specific to each Branch and Fiscal Year) by the total units sold. The rate value is stored in a static table (F0902) that is not directly related to my data model. Therefore, I use the TREATAS function to establish the necessary relationships.

 

Total Revenue =

VAR rate_x_meter =

    CALCULATE(

    SUM(F0902'[Rate per meter]),

    TREATAS(VALUES(F1201'[ResponsibleBU]), F0902'[BusinessUnit]),

    TREATAS(VALUES(F1202-F0911'[FiscalYear]), F0902'[FiscalYear])

)

RETURN

rate_x_meter * [Units Sold]

 

My units are calculated in a separate measure, which aggregates all units sold based on a specified Period parameter.

 

Units Sold =

        CALCULATE(

            SWITCH( 'Period'[Period Value],

                    1, SUM(F1202-F0911'[Period01Balance]),

2, SUM(F1202-F0911'[Period01Balance]) + SUM(F1202-F0911'[Period02Balance]),

3, SUM(F1202-F0911'[Period01Balance]) + SUM(F1202-F0911'[Period02Balance]) + SUM(F1202-F0911'[Period03Balance]),

            ),

            FILTER(

               F1202-F0911',

                (RELATED('ObjectAccount'[AccountNumber])= "31600"

            )

)

 

My data model:

 

All subtotals by branch are ok, but the problem is the final total as you see in my first screenshot.

Could you please help me? I’ve been trying a lot, and it is driving me crazy.

 

Thanks!

 

  • mark_endicott's avatar
    mark_endicott
    1 year ago

    yzornetta - Sorry for the delay. 

     

    The suggestions made here would all work, except for the fact that your Rate per meter calculation does not work at the 3 levels (Branch Description, Unit Description, Total) when calculated inside the Total Revenue measure, hence why this does not all correctly aggregate at the total level. It therefore needs to be calculated in a separate measure, and then referenced in this measure. 

     

    This means the Total Revenue measure (according to your sample data) you need to use is:

     

     

    VAR _table =
    SUMMARIZECOLUMNS (
        'PRODCTL F0005 (Branch)'[Description],
        'PRODDTA F1201'[Unit - Description]
    )
RETURN
    IF (
        HASONEVALUE ( 'PRODCTL F0005 (Branch)'[Description] ),
        SUMX ( _table, - ( [Rate per meter] * [Units Sold] ) ),
        SUMX (
            ALL ( 'PRODCTL F0005 (Branch)'[Description] ),
            - ( [Rate per meter] * [Units Sold] )
        )
    )

     

     

    See below for expected total:

     

     

    Please accept this as the solution so others with the same challenge can find the answer. 

9 Replies

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

    yzornetta - The SUMX for the total is calculating at the Aggregate level, it will not iterate throught the rows of your table unless you tell it to. I suggest you use a pattern like this:

     

    VAR _table =
    ADDCOLUMNS (
        SUMMARIZE ( 'Branch', 'Branch'[Column 1], 'Branch'[Column 2] ),
        "units", [Units Sold],
        "rate_x_meter",
            CALCULATE (
                SUM ( 'F0902'[Rate per meter] ),
                TREATAS ( VALUES ( 'F1201'[ResponsibleBU] ), 'F0902'[BusinessUnit] ),
                TREATAS ( VALUES ( 'F1202-F0911'[FiscalYear] ), 'F0902'[FiscalYear] )
            )
    )
RETURN
    SUMX ( _table, [rate_x_meter] * [units] )

     

    If this works, please accept it as the solution to help others with the same challenge. 

    • yzornetta's avatar
      yzornetta
      Regular Visitor
      1 year ago

      Hello! thanks for your time, unfortunately I tried it but it is still the same:

       

       

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

        yzornetta You have two columns in your matrix, but only one in the SUMMARIZE, please try adding the additional column. 

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

    yzornetta First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

    This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

    Also: https://youtu.be/uXRriTN0cfY
    And: https://youtu.be/n4TYhF2ARe8

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from Greg_Deckler  and mark_endicott , please allow me to add some more information:


    Hi  yzornetta ,

     

    Measure follows the context of the "Total" row and is calculated in that context. Therefore, using a measure in a column of a table visualization may have unexpected values in the "Total" column.

     

    You can use the IF()+HASONEVALUE() function to determine whether it is “Row subtotals” or “Row grand total”, and if it is, you can add as many columns as you want. If it is, you can add the columns that appear in the visual to do the calculation, such as the average, Sum and so on.

     

    You can try the following dax and replace the table and column names that appear below with the corresponding column names that appear in visual.

    Measure =
IF(
    HASONEVALUE('Table'[Description]),SUMX('Table',[Total Revenue]),
IF(
    HASONEVALUE('Table'[Branch]),SUMX(FILTER(ALL('Table'),[Branch]=MAX([Branch])),[Total Revenue]),
    SUMX(
      ALL('Table'),[Total Revenue]))
    )

     

     

    Best Regards,

    Liu Yang

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