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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sme12345
Regular Visitor

Join 3 sliced visuals into one combo chart

I have a dataset of dates and amounts which are classified by 1) location, 2) market and 3) brand.  A given location may belong to 1+ markets and 1+ brands.

I have one slicer on the location field; when user selects a location, I want a combo chart to display 1) the average location amount per month as bars, and also to display 2) the average market amount per month as a line, and 3) the average brand amount per month as a second line.

I can only have a location slicer - it is not possible to have a location slicer, a market slicer and a brand slicer as I would normally prefer.

I've gotten partway there using the following steps:

1) Renamed the dataset as Data - Location, and copied it over to Data - Market and also Data - Brand using DAX
2) Added dataset Xref containing the distinct list of Location, Market and Brand fields
3) In data model I connected Xref to Data - Location (using the Location field), and to Data - Market (using the Market field), and to Data - Brand (using the Brand field)
4) Added a slicer with field Xref[Location]
5) Added a bar chart using the date and amount from Data - Location
6) Added a line chart using the date and amount from Data - Market
7) Added a line chart using the date and amount from Data - Brand

Now when I select a location from the slicer, all 3 charts automatically update accordingly - this is close to what I want.

However, I want all 3 amounts to show up in one combo chart with a bar and 2 lines - and nothing I do seems to work.  In the above scenario, everything is aggregated by date (x axis) and amount (y axis), so it looks like it should be doable, but as far as I know you can't build off of visuals, so I need to find a way to do this with creative table relationships and DAX statements.

Please let me know how I can accomplish this.

Fig 1 - Data Model:

sme12345_0-1670374047712.png
Fig 2 - What I've accomplished so far:
sme12345_1-1670374106883.png

Fig 3 - What I'm trying to accomplish:

sme12345_3-1670374337264.png

-sme12345

3 REPLIES 3
sme12345
Regular Visitor

Also, super imposing the 3 charts over one another is not an option here - all 3 amounts (location, market and brand) must appear in the same combo chart.

v-binbinyu-msft
Community Support
Community Support

Hi @sme12345 ,

As your third picture shows, isn't this exactly the result you were hoping for? It seems you have achieved it.

 

Best regards,
Community Support Team_Binbin Yu

 

No, the third picture is a mock-up showing how i *want* it to look:

"Now when I select a location from the slicer, all 3 charts automatically update accordingly - this is close to what I want.

However, I want all 3 amounts to show up in one combo chart with a bar and 2 lines - and nothing I do seems to work. "

Apologies if this was unclear.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.