Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
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,
Solved! Go to 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.
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.
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.
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.
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.
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
Hi,
Thank you for your feedback.
If you want to keep the department filter even when you do not select, please try the below.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |