Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
76 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
61 | |
60 |