Clustered and stacked chart sorted by two columns

[robertokzl]

Hello all, 

I'm trying to use a clustered and stacked chart to compare 2 segments of same company in 2019 and 2020, and after sort by value and company name

I already have 2020 in a stecked chart divided by company and segment.

How can i insert 2019 at same chart? 

Both file are avaliable in:

2019 - http://www.anfavea.com.br/docs/siteautoveiculos2019.xlsx 

2020 - http://www.anfavea.com.br/docs/siteautoveiculos2020.xlsx

at table IV. ( in itens Automóveis (Passanger Vehicles) and Comerciais leves (Light commercial Vehicles)

Thanks,

[v-deddai1-msft]

Hi @robertokzl ,

In Power BI Desktop, it’s not possible to create a chart which can combine both Clustered and Stacked column chart together. The expected result as you posted can’t be achieved.

As a workaround, as mentioned by AllisonKennedy , create a DimDate table and create a custom hierarchy like: year - company. Use it in x-axis, and you can use drill down to view detailed message.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[AllisonKennedy]

Can you please draw a picture of your desired result? How do you want 2019 displayed on the existing chart? You already have a lot of information displayed there, so adding more will make it difficult to read, though you could potentially look at trying to add DimDate[Year] to the Axis underneath Company Name, then drill down to expand each company name to display both years. This will impact your line visual though, so not sure how you want this displayed, maybe Year first, then Company Name???

Do you have a DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[robertokzl]

Hi Allison,
I already have chart below and i just would like to see 2019 exactly like 2020 but without pareto line.

Like FCA with 2 collumns one for 2020 and other for 2019 stacked with PV and Light CV like current chart.

If i user a DimTable and drill down i can not compare 2020 and 2019 side by side with all company overview that what i expect to have!  

Thanks Allison.

[AllisonKennedy · Accepted solution]

@robertokzl 

Please see the below photo for reference. You need to click the three dots on the visual to change it to sort by Company Year, and expand the drill down to the year level in the hierarchy. In the screenshot below SalesTerritoryCountry = Company, Quarter=Year, Color= whatever column gives you PV and light CV

Other options is to use a custom visual from marketplace, some of these allow tiling of the visuals with the small multiple feature which may be more in line to what you want: https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381825?tab=Overview

[v-deddai1-msft]

Hi @robertokzl ,

In Power BI Desktop, it’s not possible to create a chart which can combine both Clustered and Stacked column chart together. Because it can’t determine which group of series need to be “clustered”, which group of series need to be “stacked”. And it’s still not supported to add multiple fields into Legend of a chart.

Please vote for the similar idea： https://ideas.powerbi.com/ideas/idea/?ideaid=db76a137-4324-4d6c-b5c5-cb85f9b6950a to improve the power bi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai