Create chart, that shows total, but hide 2 members of dimension

[MaSo1996]

Hello everyone,

I'd like to create a chart, that presents realisation of the plan in 2025 - I have 7 members of sales division and together they made 10 mln - I would like to create a chart, that shows sales for each salesman, but I don't want 2 of them to be displayed in the chart - so basicly:

Sales 2025	10 000 000,00 zł
Salesman 1	1 000 000,00 zł
Salesman 2	2 000 000,00 zł
Salesman 3	1 500 000,00 zł
Salesman 4	700 000,00 zł
Salesman 5	230 000,00 zł
Salesman 6	3 000 000,00 zł	not visible
Salesman 7	1 570 000,00 zł	not visible

Is it possible to achieve that?

[marcus_sommer]

Excluding certain dimension-values might be done with something like:

sum({< Salesman -= {6, 7}>} Sales)

but if some values should be hidden but included in the totals it becomes more complicated and it may need approaches like:

if(dimensionality() = 0, sum(Sales),
   sum({< Salesman -= {6, 7}>} Sales))

which may also cause some confusion in the interpretation. An alternatively may be to adjust the dimension with:

if(match(Salesman, 6, 7), 'Other', Salesman)

[Daniel_Castella]

Hi @MaSo1996 

I think your best shot is to exclude the Salesman 6 and 7 in the Set Analysis:

sum({<Salesman-={'Salesman 6','Salesman 7'}>}Sales)

And then write as Dimension label "Sales 2025" for Salesman column and "=sum(total Sales)" for Sales column. You will end with a table like this:

You can also aggregate the data in the backend and concatenate it to the main table, but it is not very fancy to do it in that way. 

Let me know if this works for you.

Kind Regards

Daniel

[Marijn]

Hi @MaSo1996 ,

You can achieve this by using this set expression:

=if(sum({$<Name-={'Salesman6','Salesman7'}>}Sales)>0,
sum(Sales))

This calculates sales for all, but doesn't show it for Salesman6 and Salesman7. This way your Total includes all.

You'll get a table that looks like this:

If you turn off the table setting Add-ons/Data handling/Include zero values:

It will look like this:

Hope this helps!