calculate percentage over dimensions in pivot

[#1]

Hello community,

have a look a the following table screenshot:

Is there a way to calculate the percentage like in this example?

Classification, Overdue and Week are my dimensions.

My calculation works for the amount of each week but I can't get the percentage to not use the total amount as the percentage basis.

Thank you,

Thorsten

[#2]

Thorsten,

Did not really catch what you want.

Sum(Total <Dimensions> Measure)  : this create a "temp table" with totals dimensionned by the different dimensions you set in the brackets

in your case, try sth like: Sum(Measure)/Sum(Total <OverDue, Week> Measure)  and express this number in Percent

Fabrice

[#3]

Hello Fabrice,

I had to rewrite my expression so that I can show a range of previous weeks based on the selected week.

Here's the sum calculation for each week:

SUM({<Year=, Week=, WeekDate={'>=$(=WeekStart(Max(WeekDate), - ($(vSetRange)-1)))<=$(=WeekEnd(Max(WeekDate)))'}>} ($(vAmount)))

I've tried your expression example for the percentage but it only works for the selected week. With works I mean that the total in % is always 100 for both classification values - just like expected.

How do I need to modify above calculation to also get the values in percent?

Thank you,

Thorsten

[sunilkumarqv]

The solution is to use aggregation in the expression which calculates percentages:

expr 1: Total Sales: sum(Measure)

expr 2: Percentage:  sum(Measure)/aggr(NODISTINCT sum(Measure),Classification, Overdue , Week)