cancel
Showing results for
Did you mean: 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   