cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vengadeshpalani
Helper III
Helper III

compare Sales person sales vs Department Avg sales in bar chart

Hi all,

 

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

 

vengadeshpalani_0-1617963285082.png

 

vengadeshpalani_1-1617963314470.png

 

Expected Output.

vengadeshpalani_2-1617963360850.png

 

i have Department & Sales Person Filter

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

 

 

Data 

DepartmentSales PersonSales
D1S15
D1S210
D1S315
D1S413
D2S512
D2S611
D2S78
D3S89
D3S917
D3S1021
D3S1112
D3S1214
D3S1316
1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @vengadeshpalani 

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:

v-robertq-msft_0-1618213855062.png

 

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

v-robertq-msft_1-1618213855071.png

 

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

v-robertq-msft_2-1618213855077.png

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.

View solution in original post

4 REPLIES 4
v-robertq-msft
Community Support
Community Support

Hi, @vengadeshpalani 

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:

v-robertq-msft_0-1618213855062.png

 

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

v-robertq-msft_1-1618213855071.png

 

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

v-robertq-msft_2-1618213855077.png

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.

View solution in original post

Jihwan_Kim
Community Champion
Community Champion

Hi, @vengadeshpalani 

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

All measures are in the sample pbix file.

 

Picture1.png

 

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.


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.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

amitchandak
Super User IV
Super User IV

@vengadeshpalani , 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]

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

 

Thanks @amitchandak 

 

when i click Department filter its working fine

vengadeshpalani_3-1617965140014.png

 

when i select Seles person it show wrong data 

vengadeshpalani_4-1617965196973.png

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors