cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
query_lord_1
Frequent Visitor

Measure Totals & subtotals not adding correctly in hierarchy

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
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1680505313856.png

 

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

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1680505313856.png

 

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors