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
siddhantk989
Helper III
Helper III

Need to Display multiple values on x axis

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

5 REPLIES 5
parry2k
Super User
Super User

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.

rowdown.png


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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???

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.