Forum Discussion
Weighted Average based on Slicer Selection
Hi PowerBI Community,
I am looking to create a card which shows the weighted average based on slicer selection (Market). Below is a sample of the data I am working with:
With this data I want do do a weighted average of the CPM metric based on the total number of impressions for that market and further by the segment for each campaign. For example, I want to be able to see the weighted average CPM for the Netherlands (if selected in the market slicer) based on the total number of impressions for Broad Segments in the campaigns for the Netherlands. And then another card that shows the weighted average CPM for the Netherlands based on the total number of impressions for Precision segments in the campaigns for the Netherlands. The campaign segments with a higher proportion of the total impressions should therefore give a higher weighting to the average CPM for that market.
I hope this makes sense.
Thanks in advance for the help!
Tommy
| Market | Campaign | Segment | Impressions (mn) | CPM |
| Netherlands | A | Broad | 6,533,100 | € 4.67 |
| Netherlands | A | Precision | 5,922,000 | € 4.31 |
| Netherlands | B | Broad | 6,736,700 | € 4.72 |
| Netherlands | B | Precision | 5,998,300 | € 4.10 |
| Netherlands | C | Broad | 15,096,500 | € 3.66 |
| Netherlands | C | Precision | 15,116,400 | € 2.61 |
| USA | A | Broad | 67,522,000 | € 1.84 |
| USA | A | Precision | 59,547,000 | € 2.08 |
| USA | A | Precision | 71,832,000 | € 1.73 |
| South Africa | A | Broad | 15,441,025 | € 0.80 |
| South Africa | A | Precision | 12,355,230 | € 1.00 |
| South Africa | B | Broad | 12,255,327 | € 1.21 |
| South Africa | B | Precision | 11,319,853 | € 1.31 |
| South Africa | C | Broad | - | |
| South Africa | C | Precision | - | |
| South Africa | D | Precision | 4,890,000 | € 1.14 |
| South Africa | D | Precision | 412,000 | € 1.49 |
| Netherlands | D | Broad | 184,000.00 | € 8.26 |
| Netherlands | D | Precision | 4,491,000.00 | € 3.09 |
| Netherlands | D | Precision | 6,106,860.00 | € 2.71 |
| Netherlands | D | Precision | 1,670,900.00 | € 3.95 |
Hi Anonymous ,
You can create below 3 measure to get the weighted average:
Measure = SUM('Markets'[Impressions (mn)])*SUM('Markets'[CPM])Impression*CPM = sumx('Markets',[Measure])Weighted Average = [Impression*CPM] / SUMX ( 'Markets', 'Markets'[Impressions (mn)] )And I just update my sample file to create the above measures, you can download it from this link.
Best Regards
Rena
6 Replies
Hi Anonymous ,
It seems you want to get the average of CPM based on different market and segment... Could you please check if the below screen shot is what you want?
If the above screen shot is not your expected result, please provide your expected result in form of table or screen shot and explain more detail. Thank you.
Best Regards
Rena
Hi Rena, Anonymous
Thanks for getting back to me on this!
Yes that is the view that I want to have exactly. Does the formula weight the averages based on the impressions that each of the lines have so that the lines with higher impressions, the average is weighted higher towards them? I don't want a straight average as that does not take into account that campaigns with more impressions should impact that average greater, but from a quick calculation it doesnt look like a straight average which is good.
Could you share the Dax formula that you used?
Thanks,
Tommy
Hi Anonymous ,
Actually, I just used the system's own average function without creating any DAX formula, you can get my sample file from this link. If it is not your expected result, please provide your expected result in form of screen shot or a table and describe more details about it. Thank you.
Best Regards
Rena