cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AaronSC Frequent Visitor
Frequent Visitor

visual slicer and calculate filter on the same column

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.

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AaronSC Frequent Visitor
Frequent Visitor

Re: visual slicer and calculate filter on the same column

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)
5 REPLIES 5
Community Support Team
Community Support Team

Re: visual slicer and calculate filter on the same column

Hi AaronSC,

 

Have you change the interactions between visuals?

Capture.PNG 

 

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

AaronSC Frequent Visitor
Frequent Visitor

Re: visual slicer and calculate filter on the same column

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

 

 

AaronSC Frequent Visitor
Frequent Visitor

Re: visual slicer and calculate filter on the same column

 

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.

AaronSC Frequent Visitor
Frequent Visitor

Re: visual slicer and calculate filter on the same column

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)
Community Support Team
Community Support Team

Re: visual slicer and calculate filter on the same column

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)

Capture.PNG 

 

Regards,

Jimmy Tao