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.
Good evening all!
I have a problem in front of me that i am struggling to solve. I have created a Matrix to show the details of a campaign down to the Average cost per Unit for that campaign. Im now being asked to show the difference between the Regions average and the Overall Campaign average. I have the table built, but for the life of me can't figure out how to make the measure that will show the Constant Average. Please see sample data below.
What i have.
Campaign Name | Service Type | Country | Cost | Units | Avg cost/Unit |
Campaign 1 | Hourly | Portugal | 236.31 | 2.5 | 94.52 |
Spain | 396.88 | 4 | 99.22 | ||
Hourly Total | 633.19 | 6.5 | 97.41 | ||
Unit | Baltics | 3168.03 | 6 | 528.01 | |
Dubai | 577.06 | 1 | 577.06 | ||
Greece | 1633.07 | 3 | 544.36 | ||
Portugal | 818.61 | 2 | 409.31 | ||
Spain | 1154.12 | 2 | 577.06 | ||
Unit Total | 7350.89 | 14 | 525.06 |
What I need
Campaign Name | Service Type | Country | Cost | Units | Avg cost/Unit | Campaign Avg | Difference Against Campaign Avg. |
Campaign 1 | Hourly | Portugal | 236.31 | 2.5 | 94.52 | 97.41 | -2.89 |
Spain | 396.88 | 4 | 99.22 | 97.41 | 1.81 | ||
Hourly Total | 633.19 | 6.5 | 97.41 | 97.41 | 0.00 | ||
Unit | Baltics | 3168.03 | 6 | 528.01 | 525.06 | 2.94 | |
Dubai | 577.06 | 1 | 577.06 | 525.06 | 52.00 | ||
Greece | 1633.07 | 3 | 544.36 | 525.06 | 19.29 | ||
Portugal | 818.61 | 2 | 409.31 | 525.06 | -115.76 | ||
Spain | 1154.12 | 2 | 577.06 | 525.06 | 52.00 | ||
Unit Total | 7350.89 | 14 | 525.06 | 525.06 | 0.00 |
Its the last two columns on the second table giving me issue. Any help would be greatly appreciated! I hope i have given enough information.
Thanks again!
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
One of many ways to solve this is to use ALLEXCEPT function.
All measures are in the attached pbix file.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
One of many ways to solve this is to use ALLEXCEPT function.
All measures are in the attached pbix file.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |