% of total in cross table

[Xav Dou]

Hi,

I would like to use the aggregation "% of Total" in a cross table to basically calculate the percentage of Total income per year per shop (see table below).

However, it is not calculating correctly. It will do OVER AXIS ROWS instead of OVER YEAR:

Sum([iNCOME]) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows]))

So, i switch to the "Max" aggregation where i get the proper value. (it works also with "Min")

However, in the Sub Total per Year, it will show me theMAX value and not100%.

Do you know how should i type my formula so when i select the aggregation "% of Total", it works correctly

 

 

 

 

 

Year

Shop

Income

Total Income per Year

% of Total Income per Year per Shop

 

 

Formula

na

na

na

SUM([income]) OVER ([Year])

Max([income]) / SUM([income]) OVER ([Year])

 

 

 

2020

A

2000

3000

67%

 

 

 

2020

B

1000

3000

33%

 

 

Sub TOTAL

 

 

 

 

67%

 

 

 

2019

A

500

1200

42%

 

 

 

2019

B

700

1200

58%

 

 

Sub TOTAL

 

 

 

 

58%