cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
batranikhil
Frequent Visitor

Weighted Average of Count - Text Coulmns - Clustered Bar Chart

Hi, 

I'm looking to create a weighted Average of the count from 3 coulmns each of which represent Top 1, 2, and 3 options answered by respondents in a survey. Here's what the table looks like: 

S. No. Top1 - Most important factorTop2 - Most important factorTop3 - Most important factor
1Telco Telco Telco 
2SISISI
3App Telco App 
4Telco SITelco 
5SIApp SI
6App Telco App 
7Telco Telco Telco 
8SISISI
9App App App 
10Telco Telco Telco 
11SISISI
12Telco App Telco 
13SISISI
14App App App 
15Telco Telco Telco 
16Telco SISI
17Telco App App 
18Telco Telco SI
19Telco SIApp 
20SIApp Telco 
21Telco Telco SI
22SISIApp 
23Telco Telco Telco 
24SITelco Telco 
25Telco SISI
26SIApp App 
27Telco Telco Telco 
28Telco SISI
29SIApp App 
30App App App 

 

While I can calculate the the DISTINCT COUNT for each of the columns for individual categories (SI, Telco, and App), but I want to effectively create a Weighted Average of the Count of categories across the 3 columns - so aim is to create one Weighted Column from the three individual ones, and then display those in a Clustered Bar Chart. 

 

Weighted Avg = 3*Top1 + 2*Top2 + 1*Top3 

 

Not sure if this is doable in Power BI without much customisation, but hoping something can be done. 

 

Thanks in advance. 

 

Cheers,

Nikhil

1 ACCEPTED SOLUTION
Wimverh
Resolver IV
Resolver IV

You will have to modify your table, but this can be done in powerquery. 
With the group by function, you can create the following table
2021-07-13_08-10-14.png

 

 

 

 

 

 

 

Then you can use a simple formula to calculate the result

MyResult = sumx('Result','Result'[Count]*'Result'[Factor])

2021-07-13_08-12-18.png

 

 

 

 

 

 

sample file: https://dataploration.be/forumpost/WeightedAverage.pbix 

View solution in original post

1 REPLY 1
Wimverh
Resolver IV
Resolver IV

You will have to modify your table, but this can be done in powerquery. 
With the group by function, you can create the following table
2021-07-13_08-10-14.png

 

 

 

 

 

 

 

Then you can use a simple formula to calculate the result

MyResult = sumx('Result','Result'[Count]*'Result'[Factor])

2021-07-13_08-12-18.png

 

 

 

 

 

 

sample file: https://dataploration.be/forumpost/WeightedAverage.pbix 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!