Create Bar Chart That Compares Counts Of Values Across Multiple Columns

Question

Hi Everyone.&nbsp; I have a challenging question around creating a specific type of bar chart.&nbsp; &nbsp;Based on the data below, you can see that there are 5 Resources (column headers).&nbsp; The Row data are&nbsp; job IDs.&nbsp; The values are names.&nbsp; Any name can be used more then once per row, and with no order logic.&nbsp; &nbsp; I have a few goals with this data.&nbsp; The first goal is to produce a bar chart which counts how jobs each person participated in.&nbsp; If a name is used twice per row, i want that counted as a distinct comination of name and job ID.&nbsp;&nbsp;The table 2nd table displays the count per name of distinct job/name combinations.&nbsp; How does one accomdate this in a Power BI visual?&nbsp; What keeps throwing me off too is that i'm not really incorporating the column header into the chart (Resource 1, Resource 2 etc).&nbsp;Appreciate the help!&nbsp;

amitchandak · Answer

I think you better unpivot this data https://radacad.com/pivot-and-unpivot-with-power-bi
Can you share sample data and sample output. Mark me @

ashish_mathur · Answer

Hi,
RIght click on the Job ID number column in the Query Editor and select "Unpivot other columns".&nbsp; Drag the Value field to the visual and write this measure
=DISTINCTCOUNT(Data[Job ID])
Hope this helps.

jdpowerbi · Answer

Thanks for the response&nbsp;Ashish_Mathur&nbsp;!&nbsp; &nbsp;That seems to have solved my problem.&nbsp; However, by unpivoting the data, that seemed to have messed up the visualizations i had that were based on the data before being unpivoted.&nbsp; &nbsp; I must also add that the table described above actually has many more columns in it (probably 30 or so) that i do NOT want included in this chart visual (But are relevant for other visualization on my dashboard).&nbsp; In order to unpivot, it made sense to "Choose columns" and reduce the set of data to unpivot to only filter down to the columns that i want to unpivot).&nbsp; &nbsp; &nbsp;&nbsp;Does this mean i need multiple queries so some visualizations can be based on the unpivoted data, and others will be based on the normal set?

ashish_mathur · Answer

You are welcome.&nbsp; No, you should not need multiple queries.&nbsp; All your visuals and calculations should be based on the unpivoted dataset.

anonymous · Answer

Hi&nbsp;JDpowerBI,
Perhaps you can try to duplicate the original query table and do unpivot columns on it, save changes and return to the data view side. (you can use the category column as the relationship key to link both two tables)After these steps, you can simply create visuals based on two table fields and do interaction between different visuals.Regards,
Xiaoxin Sheng

Forum Discussion

Create Bar Chart That Compares Counts Of Values Across Multiple Columns

7 Replies

Related content

Compare one value to multiple columns values

Sequential Order column based on multiple criteria

Multiple line and clustered columns chart

Compare Multiple XML Files

Compare Dynamic Columns in a Matrix