Accumulated Percentage in Pivot Table

[#1]

Hi All,

i am trying to create an accumulated percentage in a pivot table chart. Please take a look at the charts below :

this is the output that i am expeceting :

Days	Week	W1	W2
12	12	12.50%	22.22%
23	23	37.50%	33.33%
34	34	50.00%	55.55%
45	45	75.00%	66.67%
Total		75.00%	66.67%

but when i tried it in QV it keeps showing this value (picture below), for each days it will show what is the percentage against the TOTAL.

What i am expecting is, the 2nd, 3rd, days and so on and so forth will be the accumulated with the percentage of the days before.

what i used in the Expression :

Count({$<Days={">$(=0)"}>}Nomor)

/Aggr(NODISTINCT Count(Nomor),Week)

attached is my QVW.

Really appreciate for the help guys.

Thanks in advance

[MK_QSL · Accepted solution]

Change expression as below

RangeSum(Above(Count({$<Days={">$(=0)"}>}Nomor)/Aggr(NODISTINCT Count(Nomor),Week),0,RowNo()))

This function might not give you the total at the end but if you use below you will get what you want to achieve.

IF(

     Dimensionality()=1,

          RangeSum(Above(Count({$<Days={">$(=0)"}>}Nomor)/Aggr(NODISTINCT Count(Nomor),Week),0,RowNo())),

          Aggr(Count({$<Days={">$(=0)"}>}Nomor)/Count(Nomor),Week)

    )

[#3]

Dear Manish,

thanks alot it works!

but why i am getting "0" as the first line? and maybe could you kindly help to elaborate more the reason why we use IF ( Dimensionality()=1) ?

[MK_QSL]

Dimensionality is 1 for normal rows and 0 for Total rows.

Try to add one more expression Dimensionality()  to understand it.

Without this you will get the result but not the total what you want.

Hope this helps.

UPDATE : To remove 0 for first line, go to presentation tab and select suppress Zero Values...