Excluding outside filter context

[Anonymous]

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.

MONTH	PLANT	BUSINESS	ATTRIBUTE	VALUE
Jan-20	A	Xyz	Vol	1000
Jan-20	A	Xyz	SS	20
Jan-20	A	Xyz	SS	30
Jan-20	A	Xyz	MM	40
Jan-20	A	Xyz	Var	50
Feb-20	A	Xyz	V	2000
Feb-20	A	Xyz	SS	30
Feb-20	A	Xyz	MM	40
HEIRARCHY	ATTRIBUTE
Volume	Vol
Volume	V
Sales Revenue	SS
Sales Revenue	MM
Var Margin	Var
OUTPUT
HEIRARCHY	MONTH	RATE
Sales Revenue	Jan-20	9%
Var margin	Jan-20	5%
Sales REvenue	Feb-20	4%

[v-lionel-msft]

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]

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]

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]

That did it! Thanks.

[amitchandak]

@Anonymous , Also refer : https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

[Greg_Deckler]

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.