100% Stacked Bar chart for with a Dynamic Dimension

[data_RN]

Hello All,

I am hoping I am missing the obvious here.

I am trying to finalize a 100% Stacked bar chart, it is working successfully with this measure:

Sum([Spend])/sum(total<[Cluster]>([Spend]))

Now of course folks want to be able to drill down into the Cluster to see products, but still using a 100% stacked bar chart.  I created a Drill-Down Master Dimension, and it works to change the dimension into the next layer (Product).  Except now, the stacked bars aren't 100% and are showing the % of the Total of Cluster.  Is there way to setup the measure to change as you drill down into the dimension?

This isn't right, but something like this:  If dimension is CLUSTER, Sum([Spend])/sum(total<[Cluster]>([Spend])), if dimension is PRODUCT, Sum([Spend])/sum(total<[Product]>([Spend]))

Is this possible?

Thanks as Always!!!

[Patrik_Lundblad]

Hi,

By just looking at your expression you could replace the Dimension with GetObjectField(0) and that should work when the dimension changes.

You could also try out the relative modifer in April 2020 release which has a UI for creating expressions for this.

Cheers,

Patrik.

[data_RN]

HI Patrick,

Thanks for looking at this. I will be curious to see the new UI feature, for now, I need to try it in the existing version.

Native question, should I just directly replace it into the existing formula like this:

Sum([Spend])/sum(total<GetObject Field(0)>([Spend]))

[Patrik_Lundblad]

Hi,

Yes exactly like that. 

Although it might be Getobjectfield(1) that you need. 

What it does is to look at the hypercube for the chart and it picks the column depending on the index sent in. So 0 is the first column and 1 is the second and then puts that name into the expression. That way you don't have to actually have the name of a dimension, just the position in the chart.

In your case the stack is index 0 and the segments in the stacks is 1.

Cheers,

Patrik.

[data_RN]

Hi Patrick, 

I am hoping I just have a syntax problem.  When I apply that formula as is, I get an syntax error.

Sum([Spend])/sum(total<GetObject Field(0)>([Spend]))

I tried fixing it and then my results are blanked out.  Any idea what I have wrong in the formula?

Sum([Spend])/sum(total(<GetObject Field(0)>([Spend])))

Thanks for any assistance 🙂

[Patrik_Lundblad · Accepted solution]

Hi,

It was a bit more complicated than I expected to try to write it, so i cheated and created an app with the modifier.

Here is the suggested expression, and yes it will show an error in expression editor. Just ignore it.

(Sum(Spend))/Sum(Total<[$(=Replace(GetObjectField(0),']',']]'))]> Aggr((Sum(Spend)),[$(=Replace(GetObjectField(0),']',']]'))],[$(=Replace(GetObjectField(1),']',']]'))]))

And attached an app with it.

Cheers,

Patrik.

[data_RN]

Thanks Patrik! It works perfectly! Thank you every so much for setting this up. I would have never figured that one out on my own 😄