Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

@Anonymous 

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

Appreciate your Kudos.

 

Anonymous
Not applicable

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? 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.