Forum Discussion
Creating a fixed average that doesn't filter
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!
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.
1 Reply
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.