Measure Totals & subtotals not adding correctly in hierarchy

[query_lord_1]

Hello all,

experiencing a common problem, but have yet to find a solution for my specific case. I have a matrix with has a hierarchy for rows and I can't get the totals and subtotals to add correctly.

Prev Volume = Volume in 2021

Spread = Spread 2022

Volume Difference is Sum 2022 - Sum 2021, same for spread difference

Volume Contribution is Volume Diff * Spread (Measure)

Spread Contribution is Spread Diff * Previous Volume (Measure)

The relevant parts of the matrix are as follows

                                 Volume diff,  Spread Difference, Prev Volume, Spread, Volume Contrib, Spread contrib

Trader A (subtotal) 

Company A                          -20                  .15                     15                .1              -2                    2.25                                     

Company B                            10                  .01                      7                 .23             2.3                  .07

Trader B (subtotal)

Company D                          45                    .09                    4                   .21              9.45                .36

Company E                           345                  .02                     54                .12               41.4               1.08

Grand Total

Traders are in a hierarchy in a level above the companys they trade with. Essentially the subtotals for the Traders, which should be a summation of all the columns for the companies they trade with, and the grand totals are incorrect. Just the Spread & Volume contributions matter in this case though.

In another matrix, with just clients, I was able to solve the issues by using the following formula

Spread Contrib SUMX(VALUES(tbl_FXO_DEALS[NAME]), [Spread Diff] * [Volume (Prev)]) and 

Volume Contrib = SUMX(VALUES(tbl_FXO_DEALS[NAME]),[Volume Diff] * [All_In_spread])  

Name column is the Companies

I have tried these two formulas to try and get the same result for the above matrix

Spread Contrib Dealer = SUMX(VALUES(tbl_FXO_DEALS[Trader Name]),[Spread Diff] * [Volume (Prev)]) and the same for volume

as well as 

Summarize Volume Contrib = SUMX(SUMMARIZE(tbl_FXO_DEALS

      , tbl_FXO_DEALS[Deal Dealer]

      ,tbl_FXO_DEALS[Name]

), [Volume Diff] * [All_In_spread])

but neither give me the desired subttoals or grans totals that are accurate.

Can any assist? Let me know if more information is needed. Thanks

[v-yangliu-msft · Accepted solution]

Hi  @query_lord_1 ,

Do you mean that the Total of each level in the matrix is not correct, you can use a combination of IF() and ISINSCOPE() functions.

Here are the steps you can follow：

1. Create measure.

Measure =
IF(
    ISINSCOPE('Table'[Group2]),
    SUMX(       FILTER(ALL('Table'),'Table'[Group1]=MAX('Table'[Group1])&&'Table'[Group2]=MAX('Table'[Group2])),[Volume diff]),
    IF(
        ISINSCOPE('Table'[Group1]),     
        SUMX(
        FILTER(ALL('Table'),'Table'[Group1]=MAX('Table'[Group1])),[Volume diff])
        ,
        SUMX(ALL('Table'),[Volume diff])
        ))

2. Result:

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