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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors