topic Re: Get total of classifications with different levels of that classification in DAX Commands and Tips

Get total of classifications with different levels of that classification

DeniseDL — Fri, 19 May 2023 12:44:17 GMT

Good morning,

I have a calculation I want to make, but I seem to missing how to do it. I am very new to Power BI.

I have a table with a column "Classification" that has several employee classification at various levels. i.e.: BI 01, BI 02...; EG 01, EG 02...

I would like to get a total number of employees in each classification. I'm not sure how to get Power BI to calculate a total of, say 'BI's or 'EG's by summing up all the levels and giving me one total for the whole classification. There are too many classifications and levels for to just filter each one.

Any help would be greatly appreciated!

Thank you!

Re: Get total of classifications with different levels of that classification

Alf94 — Fri, 19 May 2023 12:55:46 GMT

Hi DeniseDL,

I worked with this sample:

You can try this type of visual to solve your problem:

If I answered your question, please mark my post as a solution.

Best

Re: Get total of classifications with different levels of that classification

DeniseDL — Fri, 19 May 2023 13:06:49 GMT

That is great, however, we have a ton of classifications and levels that the visual would never be big enough. That is why I want to sum up all the level in the classification to show just the classification total.

i.e.: there are 1550 BI's out of 16000 employees

Re: Get total of classifications with different levels of that classification

Alf94 — Fri, 19 May 2023 13:17:19 GMT

DeniseDL,

Is it what you are looking for?

If yes, I used Power Query to extract text before delimeter (which is a space here) and obtain a new column with only AS/EG/CR. Then you can create the visual.

If I answered your question, please mark my post as a solution.

Best,

Re: Get total of classifications with different levels of that classification

DeniseDL — Fri, 19 May 2023 13:25:35 GMT

Yes! That is what I'm looking for. However, I'm not sure I understand how you got there. Would you be able to expand on your explanation?

Re: Get total of classifications with different levels of that classification

Alf94 — Fri, 19 May 2023 15:49:33 GMT

Sure!

Open Power Query to transform a little bit your data. First you can duplicate your "Classification" column. Then use the following option:

It will create two new columns:

Use the "Close and Apply" button. Now your table looks like this:

You can finally create the visual as expected:

If I answered your question, please mark my post as a solution.

Best,