Subtotals and multiple dimensions in a barchart

[#1]

Hello ,

I have a requirement where I have to create a bar chart for comparing the zone wise sale of the product , I have data in the below format  :

Data:
Load * Inline [
Zone,ProdType,Sales1
North,Beer,200
North,SD,100
South,Beer,250
South,SD,80
];

I need to create a bar chart in such a way that the subtotals are visible and a concatenated zone and product type also comes as a bar for each dimension(North/South) ,

attaching a sample .

In the bar chart (required format),a 3rd bar having sum for beer and SD sale for each zone should be visible and should come as the first bar ,this bar's label can be North for NorthBeer and NorthSD .

[sunny_talwar]

Or this:

[#3]

Hi Sunny ,

The requirement is to display the Total bar separately instead of staked ,something like the below format :

Total

TotalBeer

TotalSD

Similar to below which you have already done for zone wise division .

                   

[sunny_talwar · Accepted solution]

Alright, lets try this one more time

[#5]

Thanks for all the help Sunny , really appreciate the time you have taken to solve the problem

[#6]

Hi Sunny ,Sorry for bugging you again , I am unable to arrange the chart in a specific order like :

Total

Beer

SD

North

NorthBeer

NorthSD

but in the qvw which you have shared ,SD is coming between NorthSD and SouthSD , can you suggest a way that shifts the SD bar to the desired location (which is 3rd from top in this case).

[sunny_talwar]

Used this expression for sorting:

=Match(Pick(Dim, Zone&ProdType, Zone, ProdType, 'Total'), 'Total', 'Beer', 'SD', 'North', 'NorthBeer', 'NorthSD', 'South', 'SouthBeer', 'SouthSD')

[#8]

Thanks a lot .

Is there anyway to make this dynamic , like if I want to add more zones like east , west .

[sunny_talwar]

There might be a way, but it might be a little challenging. Will have to explore it a little

[#10]

If its challenging for you then it is nearly impossible for me , please explore if you have some spare time . TIA .

[sunny_talwar]

My friend, don't under-estimate your potential. I am sure if you spend enough time to get this done, you will end up finding a better solution than what I may propose because you have more data and information then I do.

Having said that, I will still try to look for a way to get this done.

Best,

Sunny