cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
foodbox123
Frequent Visitor

Ranking Column headings based on frequency of a value in the column

 

Hi, 

 

I have the table in PowerBI similar to the one below. 

What I'm aiming to do is rank the Column Headings based on the frequency of a value in the column.

For example, frequency of the text "Fail" in column B is 4, frequency of "Fail" in column C is 2, frequency column D is 1, column E is 5

Rank would be:

1. Is this cool?

2. Is this suitable?

3. Is this compliant?

4. Is this realistic?

thumbnail_image001.png

 

I've created a measure for each column to count the number of "Fail" 

FAILCOUNT = CALCULATE(COUNTROWS('table'),'table'[Is this sitable?]="Fail")

 

I'm not sure where to go from here..any advice would be great.

 

Thanks

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@foodbox123 

in PQ, select ID column and unpivot other columns

1.png2.PNG

create a measure

 

failcount = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Value]="Fail"))

rank = RANKX(ALLSELECTED('Table'[Attribute]),[failcount],,DESC)

 

4.PNG





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

Proud to be a Super User!




View solution in original post

1 REPLY 1
ryan_mayu
Super User
Super User

@foodbox123 

in PQ, select ID column and unpivot other columns

1.png2.PNG

create a measure

 

failcount = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Value]="Fail"))

rank = RANKX(ALLSELECTED('Table'[Attribute]),[failcount],,DESC)

 

4.PNG





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

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!