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

Count across 5 columns for same values

Hi

 

my excel data has 5 columns that contain 3 common values. (Surgery/Seles/Leding team) or are empty

 

Capture2.PNG

 

My Graph set up:

  • Axis - Hospital spec 1
  • Value - count of name

-say (Surgery/Seles/Leding team) is respectively D, I, L

data.PNG

 

 

Now how can I add remaining 4 columns to the graph to get total across all 5 surgeries ? It does not work by default

Beside there are nulls that i want to filter out. That is easy for 1 column but not sure how it would be when there is 5.

Excluding rows from one column that are null also deleate filled ones from the other . Look at the excel data above.

 

I tried play with query editor:

- pivot  reduced number of categories (Surgery/Seles/Leding team) to just 1 - not sure why

- Transpose - gives me thousand name columns

 

I was thinking of getting total count for each column per category. (would need not count nulls) but how I get it back to graph above?

 

Any idea ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Count across 5 columns for same values

@nick_88 

 

Hi, In Query Editor try this:

 

-Select the columns with Hospital Spec (1 to 5)

-Transform - Unpivot Columns

-Filter No Blanks Columns in This New Column

 

Regards

 

Victor




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

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Vvelarde Super Contributor
Super Contributor

Re: Count across 5 columns for same values

@nick_88 

 

Hi, In Query Editor try this:

 

-Select the columns with Hospital Spec (1 to 5)

-Transform - Unpivot Columns

-Filter No Blanks Columns in This New Column

 

Regards

 

Victor




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

Proud to be a Datanaut!




View solution in original post

nick_88 Frequent Visitor
Frequent Visitor

Re: Count across 5 columns for same values

Magic 🙂 Works like a charm 

 

Thank you so much

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

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: 224 members 2,301 guests
Please welcome our newest community members: