Forum Discussion

vkomarag's avatar
vkomarag
Icon for Helper III rankHelper III
9 years ago
Solved

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

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago
    Measure = (CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]>3)) - CALCULATE(COUNT([empno]),FILTER(Ratings,[rating]<3))) / COUNTROWS(Ratings)
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

    Table =
SUMMARIZECOLUMNS (
    Table1[question],
    "Rating", (
        CALCULATE ( COUNT ( Table1[empno] ), FILTER ( Table1, [rating] > 3 ) )
            - CALCULATE ( COUNT ( Table1[empno] ), FILTER ( Table1, [rating] < 3 ) )
    )
        / COUNTROWS ( Table1 )
)

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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. :smileyhappy:

 

Table =
SUMMARIZECOLUMNS (
    Table1[question],
    "Rating", (
        CALCULATE ( COUNT ( Table1[empno] ), FILTER ( Table1, [rating] > 3 ) )
            - CALCULATE ( COUNT ( Table1[empno] ), FILTER ( Table1, [rating] < 3 ) )
    )
        / COUNTROWS ( Table1 )
)

 

Regards