Forum Discussion
Manipulation of Row Level Subtotal
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.
the simplistic answer would be HASONEVALUE but that can backfire badly if a parent level only has a single child.
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.
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.
I don't think PARENT is a DAX function. I tinkered a bit with EXPAND / COLLAPSE with no success.
