Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jansco
Helper I
Helper I

Custom Subtotals in Matrix for Different Categories

I’m looking for a way to calculate custom totals based on a filtered Matix.

Let’s say I have 15 variables, including the following:

A

B

B/A

C

D

C/D

For single variables a normal total is fine. For B/A the total should actually be the total of B/the total of A.

 

I know I can create measures for each of these, summing the values or dividing, but is there any way to put all of the totals neatly in one column?

 

1 ACCEPTED SOLUTION

So if you unpivot and use the disconnected table it should work fine.

 

2019-03 variables.png

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.

 

View solution in original post

12 REPLIES 12
d_gosbell
Super User
Super User

If 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>

)

Okay.  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...

 

 

 

 

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

VariableMonth

Value

Customer 1

AJan

10

Customer 1AMarch20
Customer 1AApril30
Customer 1AMay40
Customer 1BJan20
Customer 1BMarch30
Customer 1BApril40
Customer 1BMay50
Customer 1B/AJan2
Customer 1B/AMarch1.5
Customer 1B/AApril1.3
Customer 1B/AMay1.25

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?

So if you unpivot and use the disconnected table it should work fine.

 

2019-03 variables.png

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.

 

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.


@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 )

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

As I suggested earlier you can do this by adding some nested logic. So inside the "Gross Profit" condition we can then check if there are any filters on the L2 or L3 tables (if they are not filtered then we are looking at a total value).

 

eg.

 

 

Measure =
SWITCH (
    SELECTEDVALUE ( 'Sheet1'[Variable] ),
    "Gross profit",
        SWITCH (
            TRUE (),
            NOT ( ISFILTERED ( VariableL2 ) || ISFILTERED ( VariableL3 ) ), 
            12,
            SUM ( 'Sheet1'[Value] )
        ),
    SUM ( Sheet1[Value] )
)

 

 

This produces the following output
d_gosbell_1-1642456329222.png

 

 

I tried this but it returns blank. 

Anonymous
Not applicable

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors