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

Sales person sales vs Department Avg sales in bar chart

Hi All,

 

work-pbix file 

 

I have sales data like below sales table

 

DateDepartmentSales PersonSales
1-Nov-2018AA158
1-Nov-2018BB1150
1-Nov-2018BB2200
1-Nov-2018BB3135
1-Nov-2018BB4210
1-Nov-2018CC1100
1-Nov-2018CC2110
1-Nov-2018CC390
1-Dec-2018AA175
1-Dec-2018AA295
1-Dec-2018AA3210
1-Dec-2018AA458
1-Dec-2018BB1100
1-Dec-2018BB2150
1-Dec-2018BB3135
1-Dec-2018CC195
1-Dec-2018CC2110
1-Dec-2018CC3110

 

By using this sales data i have created Calender Table. so my data model is 

 

vengadeshpalani_0-1618040352165.png

 

i want to compare  Sales person sales vs Department Avg sales in bar chart & i have Month-Year, Department, Sales person Filters.

 

i'm using following code for Avg department sales

avg Dept sales = averageX(filter(allselected('Sales Table'), [Department] =max([Department])),[sales])

 

when i select Month-year & Department filter, it's show correct data in bar chart

vengadeshpalani_1-1618040533200.png

 

when i select Sales Person, it show wrong data. expected output was highlighted in red mark 

vengadeshpalani_3-1618040773605.png

 

 

Please help me to resolve this issue

 

Thanks,

 

 

1 ACCEPTED SOLUTION

Hi,

Thank you for your feedback.

If you want to keep the department filter even when you do not select, please try the below.

 

avg Dept sales =
IF (
ISBLANK ( SUM ( 'Sales Table'[Sales] ) ),
BLANK (),
CALCULATE (
AVERAGEX ( 'Sales Table', 'Sales Table'[Sales] ),
REMOVEFILTERS('Sales Table'[Sales Person]), VALUES('Sales Table'[Department])
)
)
 
If you also want to keep a filter even when you do not select the time, you can add one more condition like above.
 

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.

View solution in original post

5 REPLIES 5
Jihwan_Kim
Community Champion
Community Champion

Hi, @vengadeshpalani 

 

Please try the below measure.

 

avg Dept sales =
CALCULATE (
AVERAGEX ( 'Sales Table', 'Sales Table'[Sales] ),
REMOVEFILTERS ( 'Sales Table'[Sales Person] )
)

 

 

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.

Thank you @Jihwan_Kim ,

 

"B4" did not present in "B" Department at Dec-2018. but bar chart show record for B4.

 How can i avoid this. 

vengadeshpalani_0-1618044161372.png

 

Hi, @vengadeshpalani 

Thank you for your feedback.

Please try the below.

 

avg Dept sales =
IF (
ISBLANK ( SUM ( 'Sales Table'[Sales] ) ),
BLANK (),
CALCULATE (
AVERAGEX ( 'Sales Table', 'Sales Table'[Sales] ),
REMOVEFILTERS ( 'Sales Table'[Sales Person] )
)
)

 

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.

@Jihwan_Kim 

 

its working perfetly, if we select any one filter in Department

 

vengadeshpalani_0-1618046446265.png

If we removed Department filter, it show wrong data

 

Ex: B1,B2,B3,B4 are in B Department. so their Department avg is 173.75. But it show 131.63 for all sales person

vengadeshpalani_1-1618046486586.png

 

Hi,

Thank you for your feedback.

If you want to keep the department filter even when you do not select, please try the below.

 

avg Dept sales =
IF (
ISBLANK ( SUM ( 'Sales Table'[Sales] ) ),
BLANK (),
CALCULATE (
AVERAGEX ( 'Sales Table', 'Sales Table'[Sales] ),
REMOVEFILTERS('Sales Table'[Sales Person]), VALUES('Sales Table'[Department])
)
)
 
If you also want to keep a filter even when you do not select the time, you can add one more condition like above.
 

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.

View solution in original post

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 Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors