Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Excluding outside filter context

Hi all,

 

In the below tables I am trying to create a measure which does a calculation as shown in the Output table.

For a given Plant-Business-Month, sum of each type of heirarchy is divided by the 'sum of Volume of that month.

e.g for Jan-2020 Sales Revenue = (20+30+40)/1000 = 9% 

 

In my report I have slicers set on Plant, Business and Heirarchy. 

In the above scenario to calculate this, I want the denominator to ignore the filter context on Heirarchy coming from outside. And I am trying to plot the Months in a line chart. 
I want to be able to select Heirarchy on slicers and line chart shows me month over month rate.

 

Hope this makes sense. Your help is highly appreciated.

 

 

MONTHPLANTBUSINESSATTRIBUTEVALUE 
Jan-20AXyzVol1000 
Jan-20AXyzSS20 
Jan-20AXyzSS30 
Jan-20AXyzMM40 
Jan-20AXyzVar50 
Feb-20AXyzV2000 
Feb-20AXyzSS30 
Feb-20AXyzMM40 
      
HEIRARCHYATTRIBUTE  
VolumeVol    
VolumeV    
Sales RevenueSS    
Sales RevenueMM    
Var MarginVar    
      
      
OUTPUT      
      
HEIRARCHYMONTHRATE   
Sales RevenueJan-209%   
Var marginJan-205%   
Sales REvenueFeb-204%   

 

 

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Well, to ignore context, you would use REMOTEFILTERS or possibly ALLEXCEPT. Sometimes you need to start with ALL and then use FILTER to put the filters you want back in place. Depends.

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Try to create four measures.

    Measure = 
CALCULATE(
    SUM(Sheet1[VALUE]),
    FILTER(
        Sheet2,
        Sheet2[HEIRARCHY] = SELECTEDVALUE(Sheet2[HEIRARCHY]) && Sheet2[HEIRARCHY] <> "Volume"
    ),
    FILTER(
        Sheet1,
    Sheet1[MONTH] = SELECTEDVALUE(Sheet1[MONTH])
    )
)
    Measure 2 = 
VAR x = 
CALCULATE(
    SUM(Sheet1[VALUE]),
    FILTER(
        Sheet2,
        Sheet2[HEIRARCHY] = SELECTEDVALUE(Sheet2[HEIRARCHY]) && Sheet2[HEIRARCHY]  =  "Volume"
    ),
    FILTER(
        Sheet1,
        Sheet1[MONTH]= SELECTEDVALUE(Sheet1[MONTH])
    )
)
RETURN
x
    Measure 3 = 
VAR x = 
SUMX(
    FILTER(
        ALL(Sheet1),
        Sheet1[MONTH] = SELECTEDVALUE(Sheet1[MONTH])
    ),
    [Measure 2]
)
RETURN
x
    Measure 4 = 
DIVIDE(
    [Measure], [Measure 3],
    BLANK()
)

    (Note: Do not combine Measure2 and Measure3 into one formula, as this may cause problems.)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lionel-msft ,

       

      Thank you for the response.

       

      I think the issue here is that Measure 3 calculates the total "Volume" for a given date irrespective of outside filter say a Plant or Business. I want the meausre to ignore only Heirarchy filter but keep the Plant and Business filters. 

       

      Any thoughts? 


      Regards,

      Atul

      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Or like this?

         

        Measure 3 = 
VAR x = 
SUMX(
    FILTER(
        ALL(Sheet1),
        Sheet1[MONTH] = SELECTEDVALUE(Sheet1[MONTH]) && 
        Sheet1[Plant] = SELECTEDVALUE(Sheet1[Plant]) &&
        Sheet1[Business] = SELECTEDVALUE(Sheet1[Business])
    ),
    [Measure 2]
)
RETURN
x

         

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.