Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
1 year ago

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

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

  • 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's avatar
      smpa01
      Icon for Community Champion rankCommunity 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's avatar
        lbendlin
        Icon for Super User rankSuper User

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

  • v-kathullac's avatar
    v-kathullac
    Icon for Community Support rankCommunity 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's avatar
    v-kathullac
    Icon for Community Support rankCommunity 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's avatar
    v-kathullac
    Icon for Community Support rankCommunity 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.