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.
Super UserIf you have a table with the list of variables (which is not connected to any of your other tables) you could use this table and a measure with a SWITCH statement to bring all your values into the one column. The switch can either reference other measures or do the calculations inline.
eg
= SWITCH( SELECTEDVALUE( Variables[Variable])
, "A", <calc for A>
, "B", <calc for B>
, "B/A", <calc for B/A>
, <default calc>
)
JanscoOkay. So, I assume there is no way to do this if my list of variables IS connected to my other tables?
My table looks something like this
January
March
April
May
Customer 1
A
10
20
30
40
B
20
30
40
50
B/A
2
1.5
1.3
1.25
Customer 2...
- d_gosbell
So If you've already got the amounts calculated can't you just unpivot the data so that you have it in the following format
Customer
Variable Month Value
Customer 1
A Jan 10
Customer 1 A March 20 Customer 1 A April 30 Customer 1 A May 40 Customer 1 B Jan 20 Customer 1 B March 30 Customer 1 B April 40 Customer 1 B May 50 Customer 1 B/A Jan 2 Customer 1 B/A March 1.5 Customer 1 B/A April 1.3 Customer 1 B/A May 1.25 - Jansco
Here 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?