Forum Discussion
Custom subtotals with sort order
Hi all,
I ask for your assistance in this issue:
tried to attach sample dataset but can't...
I have multiple sources, bringing in several tables of similar structure:
- Activity – these can vary. Some will be shared across the sources, some will only come from one source.
- Attribute – is the cost direct / indirect. Each activity has two lines in the data reflecting this.
- Costs columns – same number of columns for all source tables.
Simple dataset.
After a bit of transformation I append all the tables and get one Data table which is a Cartesian product of all activities and attributes with their relevant costs.
| Activity | Attribute | Staff | Software | D&A |
| BI | Direct | 54 | 33 | 13 |
| BI | Indirect | 200 | 21 | 654 |
| CFO | Direct | 2 | 5 | 36 |
| CFO | Indirect | 34 | 3 | 6 |
| Accounting | Direct | 42 | 25 | 443 |
| Accounting | Indirect | 13 | 98 | 64 |
| TP | Direct | 76 | 43 | 75 |
| TP | Indirect | 4 | 7 | 3 |
| ESG | Direct | 7 | 5 | 65 |
| ESG | Indirect | 54 | 87 | 43 |
| BI | Direct | 21 | 14 | 13 |
| BI | Indirect | 200 | 54 | 654 |
| CFO | Direct | 45 | 34 | 21 |
| CFO | Indirect | 34 | 56 | 73 |
| 16x | Direct | 42 | 25 | 15 |
| 16x | Indirect | 13 | 31 | 12 |
| 21y | Direct | 33 | 42 | 75 |
| 21y | Indirect | 65 | 443 | 76 |
The stakeholders want a resulting report showing a single visual in the below format, with custom subtotals for attribute. These can be before or after the grand total row, whatever is easier (however totals and subtotals should be bold).
| Activity | Staff | Software | D&A | % |
| 16x | 55 | 56 | 27 | 3% |
| 21y | 98 | 485 | 151 | 17% |
| Accounting | 55 | 123 | 507 | 16% |
| BI | 475 | 122 | 1334 | 45% |
| CFO | 115 | 98 | 136 | 8% |
| ESG | 61 | 92 | 108 | 6% |
| TP | 80 | 50 | 78 | 5% |
| Total | 939 | 1026 | 2341 | |
| Direct | 322 | 226 | 756 | 30% |
| Indirect | 617 | 800 | 1585 | 70% |
I managed this with custom table for the sub/total with sort and appending it to the values of "attributes":
Display Table =
a staff measure would then look like this:
, my questions are:
- Is my solution scalable? Eventually I’ll have tens of millions of rows, not sure the implicit calculations are viable (and I absolutely hate working with calculation groups).
- My solution only works with a sort order for the subtotals and totals.
If the users sort the end visual by any other value, the totals are getting lost on in the middle.
How do I keep them at the bottom when sorting by value? - As “Activity” is dynamic and can change between files, added or removed, is there a way to handle sorting order there as well somehow?
How would you approach this?
I think this is possible with Inforiver matrix, but no one is going to give me a budget for this per user 😞
Thanks!
Hi YO_CO - Rather than using SWITCH, use a SUMX that adjusts behavior based on the current row label
Sum of Staff =
VAR CurrentRow = SELECTEDVALUE('Display Table'[DisplayActivity])
RETURN
SWITCH(
TRUE(),
CurrentRow = "Total", SUM('Appended Table'[Staff]),
CurrentRow = "Direct", CALCULATE(SUM('Appended Table'[Staff]), 'Appended Table'[Attribute] = "Direct"),
CurrentRow = "Indirect", CALCULATE(SUM('Appended Table'[Staff]), 'Appended Table'[Attribute] = "Indirect"),
-- Regular activity row
CALCULATE(SUM('Appended Table'[Staff]), 'Appended Table'[Activity] = CurrentRow)
)coming to total positions,Power BI’s biggest visual limitation: it doesn’t keep totals “sticky” when you sort by a column like Staff. you can try with sort by column approach.
you can compute total once and divide as like below:
Total_Staff = CALCULATE(SUM('Appended Table'[Staff]))
% Staff =
DIVIDE(
[Sum of Staff],
[Total_Staff]
)I hope the above approach works in your scenerio
4 Replies
- rajendraongole1
Super User
Hi YO_CO - Rather than using SWITCH, use a SUMX that adjusts behavior based on the current row label
Sum of Staff =
VAR CurrentRow = SELECTEDVALUE('Display Table'[DisplayActivity])
RETURN
SWITCH(
TRUE(),
CurrentRow = "Total", SUM('Appended Table'[Staff]),
CurrentRow = "Direct", CALCULATE(SUM('Appended Table'[Staff]), 'Appended Table'[Attribute] = "Direct"),
CurrentRow = "Indirect", CALCULATE(SUM('Appended Table'[Staff]), 'Appended Table'[Attribute] = "Indirect"),
-- Regular activity row
CALCULATE(SUM('Appended Table'[Staff]), 'Appended Table'[Activity] = CurrentRow)
)coming to total positions,Power BI’s biggest visual limitation: it doesn’t keep totals “sticky” when you sort by a column like Staff. you can try with sort by column approach.
you can compute total once and divide as like below:
Total_Staff = CALCULATE(SUM('Appended Table'[Staff]))
% Staff =
DIVIDE(
[Sum of Staff],
[Total_Staff]
)I hope the above approach works in your scenerio
Hi YO_CO
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi YO_CO
I wanted to check if you had the opportunity to review the information provided by rajendraongole1 . Please feel free to contact us if you have any further questions. If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi YO_CO
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
