Incorrect Totals even with SUMX!

[yzornetta]

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!

[v-yangliu-msft]

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.

[Greg_Deckler]

@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

[mark_endicott]

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

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

[mark_endicott]

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

[yzornetta]

Hi @mark_endicott I can not added it in the SUMMARIZE as this column is not from the Branch table, what can I do in this case?  

[yzornetta]

I updated the formula to include both columns in the SUMMARIZE, but it is still not working 😞 

[mark_endicott · Accepted solution]

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

[yzornetta]

You are my hero!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! thank you so much!! ❤️