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 t...
Need Help
v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity 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
      Icon for Community Support rankCommunity 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        That did it! Thanks.