Year over Year % Change Evolution over Total

[Vincent Thuilot]

Hello,

In a cross-table, i would like to compute % market share evolution year over year for clients appearing on the row axis .

Calculating market share on a given year is rather straightforward: 

Sum([Sales]) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows])), where the rows are the different clients (just one dimension).

But how can I calculate the market share difference Year over Year for each client and summarize it fine in a cross table?

          Example:

              - if client A has a market share of 17% in 2023 and 17.3% in 2024, I want to show +0.3% in 2024 within the cross table
              - if client B has a market share of 16.2% in 2023 and 15.3% in 2024, I want to show -0.9% in 2024 within the cross table

I tried several times but got limited as i couldn't leverage the THEN [Value] command several times. 

NB: I am not interested in the YoY sales growth, that is also pretty straightforward:

 Sum([Sales]) THEN ([Value] / First([Value]) OVER (NavigatePeriod([Axis.Columns],"Year",-1))) - 1.

I have the feeling that I missed something obvious!

Thanks in advance for your support!

Vincent
 

 

 

 

[David Boot-Olazabal]

Hi Vincent,

Can you share some data or an example dxp?

There are a few out of the box functions that might be applicable, such as Difference % YoY  or Compound Annual Growth rate. But it's best tested with some data.

Kind regards,

David

[Vincent Thuilot]

Hi David,

I attached an example of what i need, please see DXP attached. we are on 11.4 version.

As mentioned earlier, i am not interested in showing the CAGR (Compound Annual Growth Rate) or a basic Sales Growth.
What I am interested in is showing the evolution of the market share that would show totally different numbers  (Sales Growth can increase whilst MS can decrease if some other clients were performing even more on a given year).

I would like to keep it into a cross-table because it's very dynamic

 

thanks for the support, please let me know if you need anything else.

 

Vincent

YoY %MS Growth.dxp

[David Boot-Olazabal]

Hi Vincent,

With a big shout out to my colleague Rae Chen, I have an expression for you that should work.

Try this one: Sum([Sales]) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows])) THEN [Value] - First([Value]) OVER (NavigatePeriod([Axis.Columns],"Year",-1))

Kind regards,

David

Edited August 28 by David Boot-Olazabal

[Vincent Thuilot]

Wonderful, it works like a charm!
Many thanks to you and your colleague Rae Chen, it was not that straightforward!

I will use it right away for the dashboard I was working on and will use it as a reference for future calculations like these.
Very instructive to see all of these expressions working together.