Manipulation of Row Level Subtotal

[smpa01]

@AlexisOlson @CNENFRNL @bcdobbs @parry2k 

I want to be able to manipulate the subtotal on a matrix visual as per the business req.

The req tells me to show fact_max as it is currently visible now but to eliminate fact_sum from row subtotals at any level

What I tried?

I built a debugger like this which exposes the current level whether in scope and built a measure that listens to whether the current level is in scope.

So my debugger is like this

and measure is like this, works pretty well on L1

fails from L2 onwards

How can I achieve the desired outcome?

N.B. I don't want to achieve this by matching the text color to background color

Thank you in advance.
PFA sample

[v-kathullac]

Hi @smpa01 ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @smpa01 ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @smpa01 ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[AlexisOlson]

It's not too hard to write a measure that works at any particular level. The problem here is that you want a measure that returns a different value at e.g. the country level depending on whether the state level is expanded or not. I suspect this may not be possible since it's essentially asking for an inconsistent definition of a measure.

There are possible workaround with bookmarks or maybe adding a new parameter to control what levels show up (rather than using the visual's drill up/down behavior).

[smpa01]

I think the problem can be narrowed down to

if there is a way DAX can return which level is currently expanded in matirx, and based on that manipulate each measure. But I dont think DAX has a way to  return this info @jeffrey_wang 

debugger6 = 
VAR L1 =ISINSCOPE ( dim_L1[L_1] )
VAR L2 =ISINSCOPE ( dim_L2[L_2] )
VAR L3 =ISINSCOPE ( dim_L3[L_3] )
VAR tbl =
    SWITCH (
        TRUE (),
        L1 = TRUE () && L2 =FALSE(),
            TOJSON (
                SUMMARIZECOLUMNS (
                    ROLLUPADDISSUBTOTAL ( 'dim_L1'[L_1], "IsGrandTotalRowTotal" )
                ),
                -1
            ),
        L1 = TRUE ()
            && L2 = TRUE () && L3=FALSE(),
            TOJSON (
                SUMMARIZECOLUMNS (
                    ROLLUPADDISSUBTOTAL (
                        'dim_L1'[L_1],
                        "IsGrandTotalRowTotal",
                        'dim_L2'[L_2],
                        "IsDM1Total"
                    )
                ),
                -1
            ),
        L1 = TRUE ()
            && L2 = TRUE ()
            && L3 = TRUE (),
            TOJSON (
                SUMMARIZECOLUMNS (
                    ROLLUPADDISSUBTOTAL (
                        'dim_L1'[L_1],
                        "IsGrandTotalRowTotal",
                        'dim_L2'[L_2],
                        "IsDM1Total",
                        'dim_L3'[L_3],
                        "IsDM3Total"
                    )
                ),
                -1
            )
    )
RETURN
    tbl

[lbendlin]

I think ISINSCOPE can handle that but you MUST start from the bottom up, not from the top down.

[AlexisOlson]

Starting from the bottom up, we can detect the current level

current_level =
SWITCH (
    TRUE (),
    ISINSCOPE ( dim_L3[L_3] ), 3,
    ISINSCOPE ( dim_L2[L_2] ), 2,
    ISINSCOPE ( dim_L1[L_1] ), 1,
    0
)

The hard part is detecting the maximum level that is displayed in the visual. If you have that, all you need is

IF ( [current_level] = [max_level], [fact_sum] )

But I can't see how to calculate [max_level] dynamically in a subtotal row filter context.

[lbendlin]

the simplistic answer would be HASONEVALUE but that can backfire badly if a parent level only has a single child.

[smpa01]

I genuinely feel this is a problem that appears simpler than what it is. To gauge the full complexity, you need to give this a try.

[lbendlin]

Oh, I have tried, and failed, a couple of times. I am fully aware of the complexities.  In fact this is very much along the lines of the issue with field parameters.  You cannot programmatically probe in which order a report user has added field parameter values to a visual.

Potentially visual calculations via PARENT could be an option.

[AlexisOlson]

I don't think PARENT is a DAX function. I tinkered a bit with EXPAND / COLLAPSE with no success.

[smpa01]

I don't know if VISUAL CALCULATION can handle it,

but if I assume for a minute that VISUAL CALCULATION can handle it, can it handle the DAX measure to handle pre or post evaluation.

If it is pre, I am interested but if post I am not. I want regular DAX/Visual Calcuation to restrict it from even evaluating on the Row Subtotal level.

[lbendlin]

you are right, I was thinking of COLLAPSE and COLLAPSEALL.

A while back Alberto had confirmed that this was a design gap

[lbendlin]

You need to start from the lowest level up, not from the highest level down.