Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a table with sample data below.
empno | question | rating |
1 | q1 | 1 |
2 | q1 | 2 |
3 | q1 | 3 |
4 | q1 | 4 |
5 | q1 | 5 |
6 | q1 | 4 |
7 | q1 | 4 |
8 | q1 | 4 |
9 | q1 | 5 |
10 | q1 | 2 |
I need to create a derived table grouping the question and calculation is count of 4&5 ratings - count of 1&2 ratings whole divided by total count.
My output should look like
count of 4&5 ratings=6
count of 1&2 ratings=3
total count=10
question | rating |
q1 | 0.3 |
Solved! Go to Solution.
Measure = (CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]>3)) - CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]<3))) / COUNTROWS(Ratings)
Hi @vkomarag,
I need to create a derived table grouping the question and calculation is count of 4&5 ratings - count of 1&2 ratings whole divided by total count.
To create a new derived table grouping by the question, the formula below should work in this scenario.
Table = SUMMARIZECOLUMNS ( Table1[question], "Rating", ( CALCULATE ( COUNT ( Table1[empno] ), FILTER ( Table1, [rating] > 3 ) ) - CALCULATE ( COUNT ( Table1[empno] ), FILTER ( Table1, [rating] < 3 ) ) ) / COUNTROWS ( Table1 ) )
Regards
Hi @vkomarag,
I need to create a derived table grouping the question and calculation is count of 4&5 ratings - count of 1&2 ratings whole divided by total count.
To create a new derived table grouping by the question, the formula below should work in this scenario.
Table = SUMMARIZECOLUMNS ( Table1[question], "Rating", ( CALCULATE ( COUNT ( Table1[empno] ), FILTER ( Table1, [rating] > 3 ) ) - CALCULATE ( COUNT ( Table1[empno] ), FILTER ( Table1, [rating] < 3 ) ) ) / COUNTROWS ( Table1 ) )
Regards
Measure = (CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]>3)) - CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]<3))) / COUNTROWS(Ratings)
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |