cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nick_88
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 Community Champion
Community Champion

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

View solution in original post

2 REPLIES 2
Vvelarde Community Champion
Community Champion

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

View solution in original post

nick_88
Frequent Visitor

Re: Count across 5 columns for same values

Magic 🙂 Works like a charm 

 

Thank you so much

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors