cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karun_r Member
Member

Count number of records of in different columns after grouping the data in a single column

I have a table like below:

 

ColumnA  -- ColumnB -- Column3 -- Column4

      123              DEV             SRE               DBA

      123              MGR            SRE               NSE

      112              DEV             PMO             NSE

      112              DEV             PMO             NSE

 

Here ColumnA is the master column on which I want to perform the aggregated calculations. I want a result something like this:

 

A           DEV      MGR     SRE       PMO         NSE         DBA

 

123         1            1          2            0               1              1

112         2            0          0            2               2               0

 

I tried to calculate the SUM of DEV using the below formula

 

DEV = 

CALCULATE ( COUNTROWS ( 'TableName' ), 'TableName'[ColumnB] = "DEV", 'TableName'[ColumnA] ='TableName'[ColumnA] )

 

But whenever I attempt the same for other values like MGR, SRE etc I face a circulare dependecy warning even though I am not using the DEV column for calculating the values

 

Here is the example for counting my MGR count

 

DEV = 

CALCULATE ( COUNTROWS ( 'TableName' ), 'TableName'[ColumnB] = "MGR", 'TableName'[ColumnA] ='TableName'[ColumnA] )

 

Is there any better approach for this ? I am newbie in PowerBI. Started using it just today. So any pointers would be very helpful.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Count number of records of in different columns after grouping the data in a single column

Personally I would unpivot the data into something like:

 

ColumnA | Position    | Role

------------------------------
    123      |  ColumnB | DEV

    123      | Column3  | SRE

...

 

To make the analysis dynamic you could create one measure - simple count of rows, and then use a Matrix visual - put ColumnA on the rows, column Role on columns and created measure on the values. That would work even if a particular "role" (DEV, DBA, etc.) can repeat in multiple columns in the original table.

 

--

 

But if you just need to perform a quick analysis on the data as it is, and the assumption can be done that a "role" (DEV, DBA, etc.) can show up in just one column, then you can easily create all necessary measures using Quick Measures feature. Example of how DEV measure could be configured with Filtered value calculation:

 

QM.pngDEV measure

Then you can follow the above pattern for all other roles.

 

Hope this helps.

 

Cheers,

Pawel

View solution in original post

v-yulgu-msft Super Contributor
Super Contributor

Re: Count number of records of in different columns after grouping the data in a single column

Hi @karun_r,

 

You can unpivot table in Query Editor.

1.PNG

 

Alternatively, you can get the unpivot table structure via creating a calculated table.

Table1_1 =
UNION (
    SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[ColumnB] ),
    SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[Column3] ),
    SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[Column4] )
)

Based on the new table, you could use a matrix to display data, as adviced by pawelpo.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Anonymous
Not applicable

Re: Count number of records of in different columns after grouping the data in a single column

Personally I would unpivot the data into something like:

 

ColumnA | Position    | Role

------------------------------
    123      |  ColumnB | DEV

    123      | Column3  | SRE

...

 

To make the analysis dynamic you could create one measure - simple count of rows, and then use a Matrix visual - put ColumnA on the rows, column Role on columns and created measure on the values. That would work even if a particular "role" (DEV, DBA, etc.) can repeat in multiple columns in the original table.

 

--

 

But if you just need to perform a quick analysis on the data as it is, and the assumption can be done that a "role" (DEV, DBA, etc.) can show up in just one column, then you can easily create all necessary measures using Quick Measures feature. Example of how DEV measure could be configured with Filtered value calculation:

 

QM.pngDEV measure

Then you can follow the above pattern for all other roles.

 

Hope this helps.

 

Cheers,

Pawel

View solution in original post

v-yulgu-msft Super Contributor
Super Contributor

Re: Count number of records of in different columns after grouping the data in a single column

Hi @karun_r,

 

You can unpivot table in Query Editor.

1.PNG

 

Alternatively, you can get the unpivot table structure via creating a calculated table.

Table1_1 =
UNION (
    SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[ColumnB] ),
    SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[Column3] ),
    SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[Column4] )
)

Based on the new table, you could use a matrix to display data, as adviced by pawelpo.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 342 members 3,209 guests
Please welcome our newest community members: