cancel
Showing results for
Did you mean:
Highlighted Helper III

## 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 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
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted Super User IV

## Re: Create a calculated table out of a table using GROUP BY expression

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted Microsoft

## Re: Create a calculated table out of a table using GROUP BY expression

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

2 REPLIES 2
Highlighted Super User IV

## Re: Create a calculated table out of a table using GROUP BY expression

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted Microsoft

## Re: Create a calculated table out of a table using GROUP BY expression

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

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (1,427)