cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.