Pivot table issue

[Anonymous]

Hi ,

I created a pivot table with four dimensions and 2 Kpi's.

i can see the correct values, when i expand  all the dimensions at one time.

if i expand dimension by dimension wise, i am not able to see the correct kpi values.

can any one help me.

[vishsaggi]

Can you send some screenshots that depicts your issue and expected output?

[Anonymous]

The below screen shot is, when i expand pivot table at a time.

2)when i expand the pivot table by each dimension wise.Spend is coming correctly but %Spend is coming wrong.

[vishsaggi]

What is the expression for %Spend in this table ?

[Anonymous]

=if(((count(total aggr(  sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (Rank(Total  sum(TotalPaid)/v1)))

/

(count(total aggr(  sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.1,'0-10%',

Repeating the formula for all %s upto 90-100%, v1 is an Variable.

[vishsaggi]

Can you share a sample to look into?

[Anonymous]

How to add the sample app, i am not able to add here.

[Anonymous]

Hi ,

Please find the  attached one.

[Anonymous]

shubham.singh‌

[vishsaggi]

Thats strange, when i expand all it give me correct %'s but when i expand individually it takes the last value 90-100%.

And as you are already getting the Total count for your time in your variable v1, for your Spend% why dont you use this?

=if(((v1) - (Rank(Total  sum(TotalPaid)/v1))) / (v1)<0.1,'0-10%',

if(((v1) - (Rank(Total  sum(TotalPaid)/v1)))  / (v1)<0.2,'10-20%',

if(((v1) - (Rank(Total  sum(TotalPaid)/v1)))  / (v1)<0.3,'20-30%',

if(((v1) - (Rank(Total  sum(TotalPaid)/v1)))  / (v1)<0.4,'30-40%',

if(((v1) - (Rank(Total  sum(TotalPaid)/v1)))  / (v1)<0.5,'40-50%',

if(((v1) - (Rank(Total  sum(TotalPaid)/v1)))  / (v1)<0.6,'50-60%',

if(((v1) - (Rank(Total  sum(TotalPaid)/v1)))  / (v1)<0.7,'60-70%',

if(((v1) - (Rank(Total  sum(TotalPaid)/v1)))  / (v1)<0.8,'70-80%',

if(((v1) - (Rank(Total  sum(TotalPaid)/v1)))  / (v1)<0.9,'80-90%','90-100%')))))))))