Forum Discussion
Manipulation of Row Level Subtotal
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
14 Replies
- lbendlin
Super User
You need to start from the lowest level up, not from the highest level down.
- AlexisOlson
Super User
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
Community Champion
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
Super User
I think ISINSCOPE can handle that but you MUST start from the bottom up, not from the top down.
- v-kathullac
Community Support
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
Community Support
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
Community Support
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.