cancel
Showing results for
Did you mean:
Frequent Visitor

## Need help creating dynamic weighted average of more than one category

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.

12 REPLIES 12
Super User IV

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Proud to be a Super User!

Frequent Visitor

sorry I may not have been clear.

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.

Super User IV

@ACFXaxis - Right, by how are you weighting them?

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

we are weighting them by Impressions.

Super User IV

@ACFXaxis  - 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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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

Super User IV

OK @ACFXaxis 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``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

@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

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

@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.

Super User IV

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?

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

sorry I may not have been clear.

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.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.