cancel
Showing results for
Did you mean:

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

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

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

Volume Contribution is Volume Diff * Spread (Measure)

The relevant parts of the matrix are as follows

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

Company B                            10                  .01                      7                 .23             2.3                  .07

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

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
as well as
Summarize Volume Contrib = SUMX(SUMMARIZE(tbl_FXO_DEALS
, tbl_FXO_DEALS[Deal Dealer]
,tbl_FXO_DEALS[Name]
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
Community Support

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

Community Support

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors