How would you graph this information?

[showard40k]

In the attached I have asked 7 employees if they have ever occupied ANY of the 9 specific position over the course of their 10+ year careers:

1. Superior Officer
2. Commanding Officer
3. Delegated Officer
4. Investigator
5. Charge Layer
6. Assisting Member
7. Person Charged
8. Review Authority
9. Never Acted

Do you have any recommendations on how I should go about graphing this in PowerBi? I want to show the frequency and % of each of the 9 positions in something like a graph and if not recommended, then in a table.

Note: Data used is fake.

Personal ID#	In which capacity/capacities have you acted within the Military Justice System? Officer Conducting a Summary Hearing - Superior Officer	In which capacity/capacities have you acted within the Military Justice System? Officer Conducting a Summary Hearing - Commanding Office	In which capacity/capacities have you acted within the Military Justice System? Officer Conducting a Summary Hearing - Delegated Officer	In which capacity/capacities have you acted within the Military Justice System? Investigator	In which capacity/capacities have you acted within the Military Justice System? Charge Layer	In which capacity/capacities have you acted within the Military Justice System? Assisting Member	In which capacity/capacities have you acted within the Military Justice System? Person Charged	In which capacity/capacities have you acted within the Military Justice System? Review Authority	In which capacity/capacities have you acted within the Military Justice System? Never Acted
1									Never Acted
2	Officer Conducting a Summary Hearing - Superior Officer	Officer Conducting a Summary Hearing - Commanding Office	Officer Conducting a Summary Hearing - Delegated Officer	Investigator	Charge Layer	Assisting Member		Review Authority
3							Person Charged
4	Officer Conducting a Summary Hearing - Superior Officer	Officer Conducting a Summary Hearing - Commanding Office
5	Officer Conducting a Summary Hearing - Superior Officer		Officer Conducting a Summary Hearing - Delegated Officer		Charge Layer	Assisting Member		Review Authority
6	Officer Conducting a Summary Hearing - Superior Officer	Officer Conducting a Summary Hearing - Commanding Office
7	Officer Conducting a Summary Hearing - Superior Officer	Officer Conducting a Summary Hearing - Commanding Office			Charge Layer			Review Authority

[OktayPamuk80]

Hi,

How about a box whisker plot. With you many employees, it would be than having 9 boxes with each having lots of dots.

Regards,
Oktay

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[showard40k]

Thanks Ashish. When I opened your PBIX file, I noticed you used "unpivoted other columns' under Transform Data. In my file, I am using a lot of other changes in "Transform Data" and I think "unpivoted other columns'  will affect my other graphs in my PowerBi dashboard. Is there a way to apply "unpivot other columns" in such a way that it won't affect my other graphs that are using my data? 

[Anonymous]

Your solution is so great Ashish_Mathur

Hi, @showard40k 

 If you do not want the unpivoted other columns to affect your report, you can perform the following operation on your original table in Power Query:

You can apply the super user method to this new query table, so that your original report will not be affected. At the same time, if your original table updates data, this new query table will also be updated.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

You are welcome.  No, i cannot think of another way.

[OktayPamuk80]

Hi,

you could create something like a buble matrix chart. As this is not a native visual, you might use the charticulator from the visual store. It is a very convenient and funny chart to create. I would go by having rows as Employee (1-7) and the positions (1-9). The frequency would define the bubble size. If you hover on one dot, you would get a tooltip, which you can also configure.

Else, create a matrix using same as above and use conditional formatting for coloring (as heatmap chart).

Regards,

Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[showard40k]

Thanks Oktay!

What if I have 10,000 employees? What would you recommend in this case? The only information I need to display is the total frequency and % occurences of the following across my company.

1. Superior Officer
2. Commanding Officer
3. Delegated Officer
4. Investigator
5. Charge Layer
6. Assisting Member
7. Person Charged
8. Review Authority
9. Never Acted