Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
% 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.
Solved! Go to 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)
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)
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
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.
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |