Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have an issue with my subtotals and totals in a matrix, I've seen that this is a common issue and found many solutions but I cant seem to apply them in my scenario.
I have 2 tables of data
The raw formula would be
Property Type 1 x Staff role 1 = row total
Property Type 1 x Staff role 2 = row total
Property Type 1 x Staff role 3 = row total
Property Type 1 x All Staff Roles = Subtotal
etc
So from that I am expecting an output of 9 rows and then 3 subtotals (and a overall total which i don't need)
I'm sure you already know what I'm going to say now, that my row totals are perfect but my subtotals don't calculate correctly.
I've done a fair bit of reading about this and have tried summerize and sumx etc. I am sure that is where the solution lies but I'm just not experienced enough yet to work it out.
I would expect in the Co-ordinator subtotal to have 9038.63 (not 23K)
These are my current measures used in the above
Scheme count by TYP FIX =
SUMX(
SUMMARIZE('Scheme bridge','Scheme bridge'[PRO_TYPE],"SUBTOTALSFIX",[Scheme count by TYPE]),[SUBTOTALSFIX])
TOTAL TIME FINAL SUBTOTAL FIX =
SUMX(
SUMMARIZE('Task Matrix','Task Matrix'[PRO_TYPE],"SUBTOTALSFIX",[Time by role and TYP]),[SUBTOTALSFIX])
total FIX = [Scheme count by TYP FIX]*[TOTAL TIME FINAL SUBTOTAL FIX]
Many Thanks for any guidance
Solved! Go to Solution.
Hi @Displaced_2000 ,
you can try this measure:
total FIX =
VAR _t =
ADDCOLUMNS (
SUMMARIZE ( 'Scheme bridge', 'Scheme Bridge'[PRO_ATY_DESCR] ),
"@total", [Scheme count by TYP FIX] * [TOTAL TIME FINAL SUBTOTAL FIX]
)
RETURN
IF (
ISINSCOPE ( 'Scheme Bridge'[PRO_ATY_DESCR] ),
[Scheme count by TYP FIX] * [TOTAL TIME FINAL SUBTOTAL FIX],
SUMX ( _t, [@total] )
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hello,
I've produced a file with some test data and recreated all the measures to match the above. Some of the numbers are different but the logic should be the same.
Any support with this would be appreciated. Cheers
Hi @Displaced_2000 ,
you can try this measure:
total FIX =
VAR _t =
ADDCOLUMNS (
SUMMARIZE ( 'Scheme bridge', 'Scheme Bridge'[PRO_ATY_DESCR] ),
"@total", [Scheme count by TYP FIX] * [TOTAL TIME FINAL SUBTOTAL FIX]
)
RETURN
IF (
ISINSCOPE ( 'Scheme Bridge'[PRO_ATY_DESCR] ),
[Scheme count by TYP FIX] * [TOTAL TIME FINAL SUBTOTAL FIX],
SUMX ( _t, [@total] )
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Many thanks, this worked perfectly 😀