Create a calculated table out of a table using GROUP BY expression

[vkomarag]

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

[Greg_Deckler · Accepted solution]

Measure = (CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]>3)) - CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]<3))) / COUNTROWS(Ratings)

[v-ljerr-msft · Accepted solution]

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

[Greg_Deckler · Accepted solution]

Measure = (CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]>3)) - CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]<3))) / COUNTROWS(Ratings)