Subtract columns in pivot table

[itcapability]

Hi,

I am struggling to subtract pivot table values from each other.

Dimension row: postcode

Dimension column: provider

Measure: avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

(I am excluding the Providers beginning Vi)

eg.

Postcode	Provider A	Provider B	Provider C
AB1	4	6	8
AB2	10	8	6
AB3	5	7	3
AB4	8	6	4

I want to subtract all providers from Provider C, which would give:

Postcode	Provider A	Provider B	Provider C
AB1	-4	-2	0
AB2	4	2	0
AB3	2	4	0
AB4	4	2	0

If I use a measure:

avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed) - avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

I get a table of zeros, as expected. However, I cannot find a way to 'fix' the second part of the set analysis to only Provider C values.  I have tried various if, if(aggr) and aggr(if) with no success. I either get zeros for Provider C and nulls for all other Providers or all nulls.

Thanks for your help,

Adrian

[dplr-rn]

Is the provider C fixed?
if so did you try

avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed) - avg({<Provider={'Provider C'},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

[sunny_talwar]

Why can't you just do this

Avg({<Provider-={"Vi*", 'C'},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

[sunny_talwar · Accepted solution]

I see what you are doing, try this

Avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed) -
Avg(TOTAL <Postcode> {<Provider-={'C'},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

[itcapability]

Thank you, sadly that doesn't work - I get nulls for everything.

I have tried variations but any values I get are incorrect and Provider C values are not zero (which they should be  )

[sunny_talwar]

Would you be able to share a sample where we can see this to be not working and do some tests?

[itcapability]

Sorry, my mistake (missed out a couple of <>).

Works a treat !! I was wondering why it was giving me values for Provider C which, when added to any other provider, gave the correct answer!

I will mark as 'solved', thanks again 

Adrian