Forum Discussion

Jansco's avatar
Jansco
Icon for Helper I rankHelper I
7 years ago
Solved

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...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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.

     

atpbi10's avatar
atpbi10
Icon for Helper I rankHelper I
4 years ago

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

d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
4 years ago

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