cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

Hi All,

 

I have a table with sample data below.

 

empnoquestionrating
1q11
2q12
3q13
4q14
5q15
6q14
7q14
8q14
9q15
10q12

 

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

 

questionrating
q10.3
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Microsoft
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. Smiley Happy

 

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

t1.PNG

 

Regards

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Microsoft
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. Smiley Happy

 

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

t1.PNG

 

Regards

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

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

Using the Community

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

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors