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
Solved! Go to Solution.
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:
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
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:
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
User | Count |
---|---|
132 | |
59 | |
55 | |
55 | |
46 |
User | Count |
---|---|
129 | |
74 | |
53 | |
52 | |
51 |