Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 factor | Top2 - Most important factor | Top3 - Most important factor |
1 | Telco | Telco | Telco |
2 | SI | SI | SI |
3 | App | Telco | App |
4 | Telco | SI | Telco |
5 | SI | App | SI |
6 | App | Telco | App |
7 | Telco | Telco | Telco |
8 | SI | SI | SI |
9 | App | App | App |
10 | Telco | Telco | Telco |
11 | SI | SI | SI |
12 | Telco | App | Telco |
13 | SI | SI | SI |
14 | App | App | App |
15 | Telco | Telco | Telco |
16 | Telco | SI | SI |
17 | Telco | App | App |
18 | Telco | Telco | SI |
19 | Telco | SI | App |
20 | SI | App | Telco |
21 | Telco | Telco | SI |
22 | SI | SI | App |
23 | Telco | Telco | Telco |
24 | SI | Telco | Telco |
25 | Telco | SI | SI |
26 | SI | App | App |
27 | Telco | Telco | Telco |
28 | Telco | SI | SI |
29 | SI | App | App |
30 | App | 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
Solved! Go to Solution.
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
Then you can use a simple formula to calculate the result
MyResult = sumx('Result','Result'[Count]*'Result'[Factor])
sample file: https://dataploration.be/forumpost/WeightedAverage.pbix
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
Then you can use a simple formula to calculate the result
MyResult = sumx('Result','Result'[Count]*'Result'[Factor])
sample file: https://dataploration.be/forumpost/WeightedAverage.pbix
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |