Forum Discussion
Matrix columns subtotal in Matrix
Hi,
I'm struggling now for a long time to add subtotals tot my matrix based on different columns.
I have three columns:
FTE : Average personnel per month
Cost: Amount of Personnel costs per month
Recovery: Amount of Recovery of Personnel
All these numbers are coming from one value, made a new calculatedtable.
| Cost C | FTE | Cost | Recovery | Total is now | Total should be |
| Management | 0.90 | 70,500 | - 50,500 | 20,001 | 20,000 |
| General | 0.80 | 32,900 | - 1,750 | 31,151 | 31,150 |
| Development | 1.00 | 124,600 | - 2,500 | 122,101 | 122,100 |
| Total | 2.70 | 228,000 | - 54,750 | 173,253 | 173,250 |
My dax formula of my measure is : Measure =
SUMX(SUMMARIZE(FTE,FTE[Cost_Centre],"Average",AVERAGE(FTE[FTE])),[Average]) + SUM('General Ledger Entries'[Amount])
Hope someone can help me with excluding the column "FTE" from subtotals!
Thanks in advance
guys for trying to help.
But luckily I were managed to do it by myself.
I used the DAX formula =HASONEFILTER
Formula: Measure =
[FTE SumX] + SUM('Appendtable CTC'[Amount]) -
IF(HASONEFILTER('GL Account Group'[Actual]),SUMX(SUMMARIZE(FTE,FTE[Employee],"Average1",FTE[FTE total]),[Average1]),[FTE SumX])With this HASONEFILTER in the matrix under FTE shows the right FTE hours and under total i don't sum up the FTE but only the Cost Recovery.
4 Replies
guys for trying to help.
But luckily I were managed to do it by myself.
I used the DAX formula =HASONEFILTER
Formula: Measure =
[FTE SumX] + SUM('Appendtable CTC'[Amount]) -
IF(HASONEFILTER('GL Account Group'[Actual]),SUMX(SUMMARIZE(FTE,FTE[Employee],"Average1",FTE[FTE total]),[Average1]),[FTE SumX])With this HASONEFILTER in the matrix under FTE shows the right FTE hours and under total i don't sum up the FTE but only the Cost Recovery.
Hi Anonymous,
It's glad that you have found the solution.
Please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
Best Regards,
Cherry
Anonymous
I am not following. Could you show us what does your matrix look like along with the xpected results?
thannks
Hi Anonymous,
Firstly, as I know, there is subtotal value for matrix in Power BI.
Based on your formula, it seems that you have two tables at least.
I'm a little confused about your scenario. If it is convenient, please share your data sample and your desired output so that we could help further on it.
Best Regards,
Cherry
