Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.