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

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.