How to aggregate items within the same measure in a bar chart

[AdrianoEllero]

I want to show per month a stacked bar chart collecting as measure the turnover of several geographical areas.
There are so many geographical areas that it would be convenient to aggregate some areas in groups, like North, East, South, and West.

How to get the stacked bar chart with the area groups instead of the singular area?

Any hint?

chart with all areas ungrouped 

[MatheusC]

Hi, @AdrianoEllero 

Create a new field in your script grouping the areas into 4 categories. I would say something like this:

// creating an inline mapping table
MapArea:
Mapping
Load * Inline [
Areas,Group
Area1,North
Area2,North
Area3,North
Area4,Sul
Area5,Sul
Area6,East
Area7,East
Area8,West
Area9,West
Area10,West
];

//*******************************************************//

// Applymap function returns the output inserted according to the mapping table above

YourTable:
Load
*,
ApplyMap('MapArea',Areas) as AreasGroup
from[](qvd);

Note: Modify to your real fields


Regards, Matheus

[Zenababy]

Hello, my dear, how are you doing today, I am Zena 24 years old girl, I saw your profile today
I like It, I decided to contact you because i have
an important issue i need to disclosed to you
privately, please try to contact me with my
email address here （ zenahammed@hotmail.com ）
if i hear from you i will tell you more about
me with my photos, thanks hoping to hear from
you, yours Zina.

[AdrianoEllero]

I guess I have no rights to create a new field...

this is all I have on the left side of the screen

[JandreKillianRIC · Accepted solution]

Hi @AdrianoEllero 

I see you don't have access to the script. You can do the grouping in a calculated dimension. 

Below is a image showing the normal bar chart, Group Bar as well as the raw data. 

Change your dimension to something like this. 

=Pick(
          Match(ActualValue, 'Val1', 'Val2', 'Val3', 'Val4', 'Val5'),
               'Category 1', 'Category 1', 'Category 2', 'Category 2', 'Category 3')

Please let me know if this works. 

[MatheusC · Accepted solution]

The script solution is where I would go.
As now said it is not possible to access the editor, so the solution presented by @JandreKillianRIC  should help on the graphical side.

- Health

[JandreKillianRIC]

Hi @AdrianoEllero 

Did this solution work for you? 

Regards - Jandre

* Please remember to mark the solution that solved your question

[AdrianoEllero · Accepted solution]

Thank you so much @JandreKillianRIC 

It works easily and perfectly

Now I would like to add a second step: some values are defined 'Val1', 'Val2', some other have to be collected all value which ends with "M" for a group M, or "R" for another one.

So I image something like this:

=Pick(
          Match(ActualValue, 'Val1', 'Val2', '', *'M', *'P'),
               'Category 1', 'Category 1', 'Category M', 'Category P')

how to collect *****M or *****P values?

Maybe using wildmatch function?