Forum Discussion
Helper ICustom Subtotals in Matrix for Different Categories
So if you unpivot and use the disconnected table it should work fine.
Note: I would hide the variable column in Table1 as the switch measure is based off the 'Variable' table. If you use the column from Table1 the measure will not work.
Helper IHere is what I see as generated by Power BI subtotals:
| January | March | April | May | Totals |
Customer 1 |
|
|
|
|
|
A | 10 | 20 | 30 | 40 | 100 |
B | 20 | 30 | 40 | 50 | 140 |
B/A | 2 | 1.5 | 1.3 | 1.25 | 6.05 (wrong!) |
But, this is what I want:
| January | March | April | May | Totals |
Customer 1 |
|
|
|
|
|
A | 10 | 20 | 30 | 40 | 100 |
B | 20 | 30 | 40 | 50 | 140 |
B/A | 2 | 1.5 | 1.3 | 1.25 | 1.4 (correct!) |
Unpivoting the data would allow me to create these totals?
Super UserSo if you unpivot and use the disconnected table it should work fine.
Note: I would hide the variable column in Table1 as the switch measure is based off the 'Variable' table. If you use the column from Table1 the measure will not work.
Hi d_gosbell ,
I tried this. Basically, I created a new table with the categories in one column ad used that in switch case.
Thanks a ton for this solution.
Although, I got confused when you mentioned "unpivot" .
I am still not sure why unpivot?
Regards,
Aishwarya- d_gosbell
So "Pivoting" is the act of taking an attribute (like the month) and creating separate columns by grouping on the values of that attribute (as in the original dataset you posted).
"Unpivotting" is the reverse of this operation where you take the grouped columns and transform them back into rows. This is what I did in my previous response to take your dataset where I transformed your dataset so that it had a single "month" column instead of a column for each month.
Sagejah9Helper III
I tried this but it returns blank.
- atpbi10
Hey thanks for this hint. It works nicely for one level matrix. How about if we have two level nested matrix? Like there are multiple variables for each of top level and I want to have custom subtotals for the top level variables but still be able to drill down and see the correspodning subvariables one level below.
I tried this solution but there are some weird things happening with the matrix table.
- d_gosbell
atpbi10 wrote:
Hey thanks for this hint. It works nicely for one level matrix. How about if we have two level nested matrix? Like there are multiple variables for each of top level and I want to have custom subtotals for the top level variables but still be able to drill down and see the correspodning subvariables one level below.
I tried this solution but there are some weird things happening with the matrix table.
It's hard to say as you have not provided any detail and I can't help fix "weird things" without details of what is going on. In theory you should be able to make this technique work across multiple levels, but it may get complicated an you may need to have multiple levels of nested switch statements.
Can you provide a link to an example pbix file with some data the shows what you are trying to do? If you can also provide the expected out comes given the example data and how you want the calculations to work we can probably help you out. (see https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 )
- atpbi10
So here is my example file. I have three levels in the matrix table. As you can see in my attempt using your solution it changes all the values for the given variable that I want to have custom total. Instead of whats happening my desired result would be to only apply the custom total on the highest level (level 1-which is "Variable" column). And if I drill down then the values would behave normally, so they would sum for the corresponding variable in the input file.
https://drive.google.com/file/d/1wC78oj6i9srmW7B0U2bUEIR7KqlNSV2N/view?usp=sharing