Create bar charts from multiple columns

[ThomasSan]

Hi everyone,

I am having sales data in the following form:

Unit	Group	Company	Sales
A1	A	C	3
A2	A	C	4
B1	B	C	2
B2	B	C	7
B3	B	C	4

As you can see company C is divided into groups A and B and these groups are comprised of units A1, A2, B1, B2 and B3.

I want to create a dashboard on unit basis where I would like to show a bar chart that benchmarks the average sales of a specific unit with the performance of the corresponding group and the company in general. It should look like this:

"Note, that I am using average sales figures here. In other words, I am not using the table above directly, but use the last column for a measure value. So it is not a question of simply unpivot the table above)

I am unsure how to order my data in order to use the PBI bar chart visual to have my (average) sales figures displayed like this. I tried using three seperate bar charts next to each other but PBI would not allow me to modify their design so that their base line are perfectly aligned like in the Excel-chart that you see above. 

I am currently thinking about creating a new table where I would use a measure calculating the average sales (depending on the set time frame) on unit, group and company level and let it fill out a new table with "Business Level" and "Average Sales" i.e. something akin to this:

Business Level	Avg. Sales
C	4
A	3.5
B	4.3
A1	3
A2	4
B1	2
B2	7
B3	4

However, I am not sure how to create a table that is being filled with measure values that respond to a time slicer that restricts the average sales figure to a specific time frame.

Does anyone know how to accomplish this?

[amitchandak]

@ThomasSan , refer if this approach can help

Show Grand total row or Avg Grand total row in Visual: https://youtu.be/OSwRZ4GUhxc

or unpivot all three column in power query and use

https://radacad.com/pivot-and-unpivot-with-power-bi

[ThomasSan]

Hi @amitchandak,

thank you for your reply. I thought unipivot would not help me with changing measures. Or am I mistaken here? 

Also, I watched your videos but are not quite sure how to apply it to my problem case. Could you please give me more input on how to adapt your solution to my problem?