query_lord
New Member
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
Totals & Grand Totals are irrelevant bc they are wrong
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
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
Totals & Grand Totals are irrelevant bc they are wrong
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