cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ACFXaxis
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

MarketDDDVMWDMWV
UK0.10.20.20.4
IT0.20.20.30.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 

MonthMarketImpsBS rate achieved
1UK100,0000.2
1IT50,0000.3
1ES200,0000.2
1DK75,0000.1

 

so new table or graph would show the following 

MonthTotal ImpsTotal BS rate achievedBS rate benchmark (dynamic based on category and country impression contribution)
1425,0000.20.2
2600,0000.30.3
3300,0000.20.4
41,000,0000.40.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
amitchandak
Super User IV
Super User IV

@ACFXaxis 

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

Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

 

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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




we are weighting them by Impressions.

 

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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 DDDVMWDMWV
UK30,00020,00040,00010,000
IT10,00010,00020,00010,000
ES40,00010,00035,00015,000
DK20,00015,00020,00020,000

 

 

(30,000/100,000* .1 +  20,000/100,000 * .2 + 40,000/100,000 * .2 + 10,000/100,000 * .4) / 4

 

 

 

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

 

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Greg_Deckler
Super User IV
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?

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors