Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

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


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

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.

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


Go to My LinkedIn Page


Anonymous
Not applicable

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, @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.

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


Go to My LinkedIn Page


Anonymous
Not applicable

@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.

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


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.