Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am creating a Line and Cluster column chart and on Y axis I am trying to show the relation between sales and units based on different mangers and years. For example if I have the below table showing the data as year, sales and Units.
Year Sales Units
2013 200 20
2012 300 30
2014 100 10
Now the thing with this graph is that I am using a custom filter for allowing the user to choose between sales and units so that he can see any one between sales and unit or both at a time. i am using the following code to display the value based on custom filter:
Display Quantity = if(contains('Show Data By','Show Data By'[Show Data],"Qty"), SUM(Sales[Quantity]), BLANK()) Display Revenue = if(contains('Show Data By','Show Data By'[Show Data],"Revenue"), SUM(Sales[Revenue]), BLANK())
Now I have one more table which has 2 columns Year and Manager.
Year Manger
2012 abc
2012 xyz
2013 abc
2014 abc
2014 xyz
On the graph I want to show the sales and Units value for each manger on yearly basis. If I am using the above code it is giving the relation between Sales, Units and Manger but it is summing up the data for all years , i.e., for manger abc it is showing me 1 bar withs sales as 600 . What I want is not to sum up the data but show the data brake down on yearly basis, i.e, instead of showing 1 bar for abc manger for sales it should show 3 bars(2012 (200), 2013 (300) and 2014 (100)) for manger abc for sales.
Note: I do not want to use drill down in this case
I believe your tables are related on Year, if you are using column chart then why not you put manager on Legend and that will show multiple items for each year by manager, is this what you are looking for?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks for the reply but putting managers on the legand doesnot solve the problem beacuse 1st i am using my legands for custom filter as well (sales and Units) and 2nd I have to show my sales and units for each manager for each year. There will be problem in displaying data when there will be multiple managers for each year
Can you share screen shots on where you see this an issue and what actual problem you are facing and what you are expecitng?
Thanks,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@siddhantk989 I don't see the aversion to the not using a the drill down, it gives you exactly what you are asking for if you use the "Expand all down one level in the hierarchy" (double down arrow). Looks like this, where breaks things out by year and manager and shows them both in the x-axis. If you are dead set against the below, you would need to create a seperate grouping dimension or calculated column to use as your x-axis.
If the sales are not somehow assigned/attributed to a Manager aren't you overstating the sales and quantity on your chart?
(when you have 2 managers in a year - they'll each show the same sales???
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |