Create a calculated rows in pivot table

[charles1981]

Hi, can anyone please help me to achieve the below:

I have one pivot table like below:

Program	Market	Model	Period	Dec-19	Feb-19	Apr-19	Jun-19	Aug-19	Oct-19	Sep-19	Jan-19	Jul-19	May-19	Mar-19	Nov-19	F/Yr Total
P81	Australia	Ca		79421	71991	66318	98506	73163	75743	78048	72208	76070	78798	79318	77044	926628
Tr		25256	19002	19947	30474	21847	22097	22258	16652	19967	23954	24400	22611	268465
Total		104677	90993	86265	128980	95010	97840	100306	88860	96037	102752	103718	99655	1195093
Total Market Industry			104677	90993	86265	128980	95010	97840	100306	88860	96037	102752	103718	99655	1195093
P82	Australia	Ca		79400	71991	66318	98506	73163	75743	78048	72208	76070	78798	79318	77044	926628
Tr		25201	19002	19947	30474	21847	22097	22288	16302	19967	23954	24400	22611	268145
Total		104601	90993	86265	128980	95010	97840	100336	88510	96037	102752	103718	99655	1194773
Total Market Industry			104601	90993	86265	128980	95010	97840	100336	88510	96037	102752	103718	99655	1194773

Now I want to create a additional calculated row in the above table to find difference of each month between the programs (first column) as below

Program	Market	Model	Period	Dec-19	Feb-19	Apr-19	Jun-19	Aug-19	Oct-19	Sep-19	Jan-19	Jul-19	May-19	Mar-19	Nov-19	F/Yr Total
P81	Australia	Ca		79421	71991	66318	98506	73163	75743	78048	72208	76070	78798	79318	77044	926628
Tr		25256	19002	19947	30474	21847	22097	22258	16652	19967	23954	24400	22611	268465
Total		104677	90993	86265	128980	95010	97840	100306	88860	96037	102752	103718	99655	1195093
Total Market Industry			104677	90993	86265	128980	95010	97840	100306	88860	96037	102752	103718	99655	1195093
P82	Australia	Ca		79400	71991	66318	98506	73163	75743	78048	72208	76070	78798	79318	77044	926628
Tr		25201	19002	19947	30474	21847	22097	22288	16302	19967	23954	24400	22611	268145
Total		104601	90993	86265	128980	95010	97840	100336	88510	96037	102752	103718	99655	1194773
P81 o/u P82	Australia	Ca		21	0	0
P81 o/u P82	Australia	Tr		55	0	0
P81 o/u P82	Australia	Total		76	0	0

like above I want to create calculated row P81 o/u P82 (P81->Australia->Ca - P82->Australia->Ca) for all the months.

[Sergey_Shuklin · Accepted solution]

Hello!

There is a solution. Please, see at this short instace:

First of all you need to load additional table in the script

After that, write your calculated dimension like this:

And expression like this:

This way will calculate sum for P* rows for dim = 1 and difference of P* rows for dim = 2.

[charles1981]

Thank you  so much.

[Kejti]

Hi Sergey!

Do you know is it possible to do that in QS? 

I've added the DIM table and first expression - what gives me a total number. But how and where can I add the second expression? 

Thanks in advance!

[Sergey_Shuklin]

Hello, Kejti

Yes you can do the same in QS.

You should create dim table as we do in QV:

Then you can create a table:

With dimension:

First value will expand stores, second will be custom calculated.

When you done with dimensions, let's take a look at expressions:

For stores we will simple get sum of sales. But for custom row we use more specific condition - sum only when flag field is equal one.

You should get such result:

If you don't like "-" in the total row, you can handle it with Dimensionality() function:

As you can see, for total row we use some untypical condition as well.

Result table:

[Kejti]

Thanks @Sergey_Shuklin  it works perfect, but I need to add more than one calculated row. Not sure, if I should create more DIM islands? Or this solution will work just for the one calc. dimension?

[Kejti]

Ok, I've just added more arguments to the Dim island and it works. Thank you very much!!