Division of two values from same column

[psk180590]

Hello All,

I have data as follows:

Market        Product         Sales      Units

AA                    1            100           5

AA                    2            130          8

AA                                   500        50

When there are no entries in the Product column it indicates the values are for the whole market.

Ex: in the above data the Sales for whole market AA is 500 and the units are 50.

I need to display this in a Pivot table with all the dimensions and the percentage of Sales and Units out of the whole market.

So, here when the user wants to see for Product 1 it has to be 100/500 = 20% and for Product 2 130/500 = 26%

But, in the Product column there should be no blank space as in the table or any other value. It should only display the Valid products (1  2 here).

TIA!!

[psk180590]

gwassenaar‌ stalwar1‌

Any pointers you could provide?

[Anil_Babu_Samineni]

From given table, What was the expected table to see?

[vinieme12]

SCRIPT

//////////////////////////////////////////////////////

Load Market,if(len(Product)=0,'ISTOTAL',Product) as Product,Sales,Units Inline [
Market,Product,Sales,Units
AA,1,100,5
AA,2,130,8
AA,,500,50
];
//////////////////////////////////////////////////////End of Script //////////////////////////////////////////////////////

Chart

dimension: MArket , Product

Expressions

sum(Sales) / Sum({<Product = {'ISTOTAL'}>}TOTAL <Market> Sales)

sum({<Product -= {'ISTOTAL'}>}Sales) 

[sunny_talwar · Accepted solution]

I think Vineeth‌ to hide blank column, would you need to add {<Product -= {'ISTOTAL'}>} to your 1st expression also?

Sum({<Product -= {'ISTOTAL'}>}Sales) / Sum({<Product = {'ISTOTAL'}>}TOTAL <Market> Sales)

[psk180590]

stalwar1‌ vinieme12‌

Thanks both for your quick suggestions!!

The first SS is based on stalwar1‌'s suggestion and the second on vinieme12‌'s suggestion.

But, the problem is i don't want 'ISTOTAL' under Product in my pivot table. Only want to display 1 & 2.

[psk180590]

Hi Anil,

You can see the expected result in the below attached SS.

[sunny_talwar]

Did you put a small negative sign in front of the equal sign?

Sum({<Product -= {'ISTOTAL'}>}Sales) / Sum({<Product = {'ISTOTAL'}>}TOTAL <Market> Sales)

[psk180590]

Ah, i missed it.

Perfect this works like charm!!

Thanks Much.

Have a great weekend.