compare Sales person sales vs Department Avg sales in bar chart

[Anonymous]

Hi all,

I have Data like below table. i want to compare Sales person sales vs Department Avg sales in bar chart

Expected Output.

i have Department & Sales Person Filter

if i selected S5 & S6  from filter it want to show S5 & S6 values only

Data 

Department	Sales Person	Sales
D1	S1	5
D1	S2	10
D1	S3	15
D1	S4	13
D2	S5	12
D2	S6	11
D2	S7	8
D3	S8	9
D3	S9	17
D3	S10	21
D3	S11	12
D3	S12	14
D3	S13	16

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description and output, I think you can calculate the value diff using calculated columns, which can avoid the error value in the chart when data is sliced you can try my steps:

1. Create a calculated column like this:

compare Sales person sales vs Department Avg sales =

var _avg=

AVERAGEX(FILTER(ALL(Data),[Department]=EARLIER(Data[Department])),[Sales])

return

[Sales]-_avg

Then set the Format of this column like this:

2. Then create a clustered bar chart and two slicers, and place them like this:

3. Then slice the data using the slicer, you can find that the data is correctly displayed:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

https://www.dropbox.com/s/qk2697spd2zhwd0/vengadeshpalani.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[amitchandak]

@Anonymous , if you need new columns

avg Dept sales = averageX(filter(table, [department] =earlier([department])),[sales])

diff = [sales] - [avg Dept sales]

if you need new measures
avg Dept sales = averageX(filter(allselected(table), [department] =max([department])),[sales])

diff = sum([sales]) - [avg Dept sales]

[Anonymous]

Thanks @amitchandak 

when i click Department filter its working fine

when i select Seles person it show wrong data