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.
Problem
We have a benchmark that has been flat all last year however this isn't accurate as each market's contribution is different and each market has a different benchmark. i.e. 2019 we averaged all benchmarks to create one that was 0.15%. we now want the benchmark to reflect that each country has its own benchmark thus if one country contributes more their benchmark should be weighted more in the overall benchmark.
The benchmarks themselves are made of 4 different columns, which is where I get stuck. I can't seem to find a way to make a weighted average of those four columns. example data below
So first I want to create an average of these benchmarks (static values that don't change) across DD, DV, MWD, MWV weighted by the amount imps that fall into each category
Market | DD | DV | MWD | MWV |
UK | 0.1 | 0.2 | 0.2 | 0.4 |
IT | 0.2 | 0.2 | 0.3 | 0.3 |
This would create a new measure called BS benchmark rate
This BS benchmark rate should then be dynamic based on the markets overall contribution to the total imps.
another table I have below
Month | Market | Imps | BS rate achieved |
1 | UK | 100,000 | 0.2 |
1 | IT | 50,000 | 0.3 |
1 | ES | 200,000 | 0.2 |
1 | DK | 75,000 | 0.1 |
so new table or graph would show the following
Month | Total Imps | Total BS rate achieved | BS rate benchmark (dynamic based on category and country impression contribution) |
1 | 425,000 | 0.2 | 0.2 |
2 | 600,000 | 0.3 | 0.3 |
3 | 300,000 | 0.2 | 0.4 |
4 | 1,000,000 | 0.4 | 0.2 |
I've tried using the weighted average quick measure but you can only use one column as the value to be averaged.
Trawled through here and stack exchange but no luck. It may be that I'm looking at the solution the wrong way but not sure.
@Anonymous
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
sorry I may not have been clear.
This is for advertising.
each country has an advertising campaign running across 4 different types of adverts. each of those types of adverts has its own benchmark which is in the first table. That's something that we are aiming for. each month the amount we run on each type can be different which is why if I just do a normal average it will be wrong because it won't take this into account.
We want to show the benchmarks as one single benchmark which is why I want to put the four together.
I then also want to show this with all the different countries put together.
i.e. Coca-cola is advertising on TV, Newspaper, Online, Magazine in the UK, Italy, Spain, and France.
for each country, I want to show the benchmark overall benchmark weighted to the amount of type of advertising it does.
then overall reporting back to central Coca-cola I want to report back on its total activity weighted to how much advertising was run per country per advertising type.
I hope this makes it clearer.
@Anonymous - Right, by how are you weighting them?
we are weighting them by Impressions.
@Anonymous - I'm not certain if you are trying to be intentionally vague or not, but you are doing a good job of it! 🙂 Or perhaps not all of the data is there.
So, in your example, in table 2 I see that there are 425,000 total impressions. So, would the calculation for the weighted average for UK be (taking the values from your first table):
(100,000/425,000 * .1 + 100,000/425,000 * .2 + 100,000/425,000 * .2 + 100,000/425,000 * .4) / 4
??
If it is not that, please be specific about how you are trying to do this because it is not fun trying to help and getting vague responses.
sorry, @Greg_Deckler I promise I'm not trying to be vague.
you are correct I'm missing the data of impressions against each type
Market | DD | DV | MWD | MWV |
UK | 30,000 | 20,000 | 40,000 | 10,000 |
IT | 10,000 | 10,000 | 20,000 | 10,000 |
ES | 40,000 | 10,000 | 35,000 | 15,000 |
DK | 20,000 | 15,000 | 20,000 | 20,000 |
(30,000/100,000* .1 + 20,000/100,000 * .2 + 40,000/100,000 * .2 + 10,000/100,000 * .4) / 4
OK @Anonymous I figured there was something missing.
I created this (PBIX is attached). Not sure it is exactly what you are looking for or not.
BM Achieved Dynamic =
VAR __Total = MAX('Impressions'[DD]) + MAX('Impressions'[DV]) + MAX('Impressions'[MWD]) + MAX('Impressions'[MWV])
VAR __DD = SUM('Impressions'[DD])/__Total * MAX('Benchmarks'[DD])
VAR __DV = SUM('Impressions'[DV])/__Total * MAX('Benchmarks'[DV])
VAR __MWD = SUM('Impressions'[MWD])/__Total * MAX('Benchmarks'[MWD])
VAR __MWV = SUM('Impressions'[MWV])/__Total * MAX('Benchmarks'[MWV])
RETURN
(__DD + __DV + __MWD + __MWV) / 4
@Greg_Deckler That seems to be the one I wanted, however, I've just realized that the data is pivoted to aggregate the impressions per campaign.
To get desktop display the there is a column for device type (desktop =1) and a column for media type (display =1)
I tried created a measure that basically said Sum impressions if desktop =1 and display = 1 but when I try it says it doesn't accept boolean.
Any help with this one or shall I make a new help question? Also is there any way to send the data privately to you?
Thanks
Andre
I am not understanding what you are asking. Might be better to open up a new thread and reference this one. You can always send data to gdeckler@fusionalliance.com
@Greg_Deckler dropped you an email as I worked it out on a spreadsheet but I'm seeing a different result that when I put it together through your measure.
Perhaps multi-column aggregation: https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...
Also, unpivoting those 4 columns in Power Query might make things easier.
However, I am not 100% on what you are going for as your weighted average. What would be the expected weighted average for UK and IT based upon your first table?
sorry I may not have been clear.
This is for advertising.
each country has an advertising campaign running across 4 different types of adverts. each of those types of adverts has its own benchmark which is in the first table. That's something that we are aiming for. each month the amount we run on each type can be different which is why if I just do a normal average it will be wrong because it won't take this into account.
We want to show the benchmarks as one single benchmark which is why I want to put the four together.
I then also want to show this with all the different countries put together.
i.e. Coca-cola is advertising on TV, Newspaper, Online, Magazine in the UK, Italy, Spain, and France.
for each country, I want to show the benchmark overall benchmark weighted to the amount of type of advertising it does.
then overall reporting back to central Coca-cola I want to report back on it's total activity weighted to how much advertising was run per country per advertising type.
I hope this makes it clearer.
The aggregated was good but I need to weight them.
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 |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |