Sales person sales vs Department Avg sales in bar chart

[Anonymous]

Hi All,

work-pbix file 

I have sales data like below sales table

Date	Department	Sales Person	Sales
1-Nov-2018	A	A1	58
1-Nov-2018	B	B1	150
1-Nov-2018	B	B2	200
1-Nov-2018	B	B3	135
1-Nov-2018	B	B4	210
1-Nov-2018	C	C1	100
1-Nov-2018	C	C2	110
1-Nov-2018	C	C3	90
1-Dec-2018	A	A1	75
1-Dec-2018	A	A2	95
1-Dec-2018	A	A3	210
1-Dec-2018	A	A4	58
1-Dec-2018	B	B1	100
1-Dec-2018	B	B2	150
1-Dec-2018	B	B3	135
1-Dec-2018	C	C1	95
1-Dec-2018	C	C2	110
1-Dec-2018	C	C3	110

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

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

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

Please help me to resolve this issue

Thanks,

[Jihwan_Kim]

Hi, @Anonymous 

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.

[Anonymous]

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. 

[Jihwan_Kim]

Hi, @Anonymous 

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.

[Anonymous]

@Jihwan_Kim 

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

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

[Jihwan_Kim · 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.