subtract data in expression

[tracycrown]

Dear all

Kindly advise how to create a 3rd column in expression to show the unit price difference between outlet A and outlet B.

 

Thanks

Tracy

[sunny_talwar · Accepted solution]

Like this?

Expression:

If(SecondaryDimensionality() = 0, Avg({Outlet<Outlet = {A}>} [Unit Price]) - Avg({Outlet<Outlet = {B}>} [Unit Price]), Avg({Outlet} [Unit Price]))

And make the following changes on the Presentation tab

[effinty2112]

Hi Tracy,

                    What about a straight table?

Item#	A	B	A - B
1	$4.50	$4.50	$0.00
2	$4.50	$4.50	$0.00
3	$7.00	$7.00	$0.00
4	$5.00	$5.00	$0.00
5	$7.00	$7.50	-$0.50
6	$7.00	$7.00	$0.00
7	$7.00	$7.00	$0.00
8	$8.50	$8.50	$0.00
9	$8.50	$8.50	$0.00
10	$15.00	$12.00	$3.00

Expression A has the formula: Sum({$<Outlet = {'A'}>}[Unit Price])

Expression B has the formula: Sum({$<Outlet = {'B'}>}[Unit Price])

Cheers

Andrew

[tracycrown]

Dear Sunny

Please advise what is the meaning of SecondaryDimensionality() = 0. What happen if I have 10 outlets and I want to show the NEW AVERAGE in 3rd column instead of just A-B.

Thank you

Tracy

[sunny_talwar]

Read about secondarydimensionality() function here: The second dimension... or how to use secondarydimensionality()

So instead of showing A-B you would want to show Average of 10 outlets? You can probably do this

If(SecondaryDimensionality() = 0, Avg({Outlet} [Unit Price]), Avg({Outlet} [Unit Price]))