Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I wan't to ignore an average which is calculated in a certain metric when there is no data available for a certain year for another metric. I have this matrix:
Year | Avg1 | Avg2 |
2017 | 15 | |
2018 | 10 | 12 |
2019 | 14 | 19 |
2020 | 12 | 12 |
total | 12 | 14.5 |
For my report, I'm calculating and presenting the overall difference between avg1 and avg2. What I wan't is when no data available for a certain year for avg1, don't calculate the average for avg2 (I'm not presenting the matrix, but only the overall). 15 is now influencing the overall comparison which is wrong. How to solve this?
The measures I'm using the calculate the averages are pretty basic: sum(sales) / distinctcount(orderid)
Hi @Anonymous
You can calculate Avg2 only when Avg1 is not blank using below logic
Avg2 = IF( ISBLANK(AVG1), BLANK(),sum(sales) / distinctcount(orderid))
i was thinking the same, but not too sure it will work when it calculates at the total level. as when the year context is removed [avg1] wont be blank so 2017 will be included? just thinking I could be wrong.
cool, it is fun seeing peoples solutions on here. always learning something new.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |