visual slicer and calculate filter on the same column

[Anonymous]

Hi all,

Baground:

Comparing one organisations pricing to everyone else in the market. Using two simple measures, one calcuates the $ difference the other calcuates the % change. See below for % change measure, with difference being built the same:

Spoiler

% Change = 
VAR __Company = CALCULATE([Average],MarketPricingTable[Organisation] = "Disney")
VAR __Competitor = CALCULATE([Average],,MarketPricingTable[Organisation] <> "Disney")
RETURN DIVIDE(__Competitor - __Company ,__Company ,0)

Problem:

Visuals do not change with a slicer using Organisation:

Finer details:

The difference measure has 1 card and 2 bar graphs.  One of the bar graphs does change when using the Organisation slicer.

The % change measure has 1 car and three line graphs. 2 of the line graphs change when disney and other companies are selected in the slicer.  < miss viewed this as the graphs were small.

Solution:

closest link online I could find was: https://exceleratorbi.com.au/filtering-column-via-visual-calculate/

The company's cacluate needs to use all finters/slicers except for Organisation.  Where as Competitor needs all filters/slicers.

Edit: removed a miscellaneous bit.

Edit 2: strikethough txt.

[Anonymous · Accepted solution]

Solution:

HA, got it:

% Change = 
VAR __company = CALCULATE(AVERAGE(Sheet1[Price]),FILTER(ALL(Sheet1[Organisation]),Sheet1[Organisation] = "DD"))
VAR __competitor = CALCULATE([Average],FILTER(ALLSELECTED(Sheet1[Organisation]),Sheet1[Organisation] <> "DD"))
RETURN DIVIDE(__competitor - __company,__company,0)

[v-yuta-msft]

Hi AaronSC,

Have you change the interactions between visuals?

 

In addtion, it seems like your measure % Change doesn't have interactions with your slicers, have you tried ALLSELECTED()? If this issue persists, could you please post some sample data or make a mock-up for further analysis?

Regards,

Jimmy Tao

[Anonymous]

Hi, here is a pbix file. I was able to duplicate the issue with % change and Diff.  However, I could not find a way to make it only work for some of the diff bar graphs.

https://drive.google.com/open?id=1SXE9Otomrk2toIaWh_vezHtGZNprJcMh

If there is a better way to share on this forum, please le\t me know.

[v-yuta-msft]

Hi AaronSC,

Modify the measure using DAX formula below and check if it can work:

% Change = 
VAR __company = CALCULATE([Average],FILTER(ALLSELECTED(Sheet1[Organisation]), Sheet1[Organisation] = "DD"))
VAR __competitor = CALCULATE([Average], FILTER(ALLSELECTED(Sheet1[Organisation]), Sheet1[Organisation] <> "DD"))
RETURN DIVIDE(__competitor - __company,__company,0)

 

Regards,

Jimmy Tao

[Anonymous]

I have not changed the interactions between visuals for any of the %/Diff visuals.

I have just tried:

% Change = 
VAR __Company = CALCULATE([Average],MarketPricingTable[Organisation] = "Disney", AllSELECTED(MarketPricingTable[Organisation]))
VAR __Competitor = CALCULATE([Average],MarketPricingTable[Organisation] <> "Disney")
RETURN DIVIDE(__Competitor - __Company ,__Company ,0)

I will, see if I can get you a mockup/sample data.

Regards,

Aaron