Variance Column based on a row with conditions

[Anonymous]

Hi, 

I am wanting to create a calculated column "Variance from Store ()" using DAX. The column will show the variance price for one row based on all the other stores in the table. 

For example:

Filterd Store: A

Store	Price	Variance from Store A
B	$30	$15
C	$45	$30
A	$15	$0
D	$20	$5

[amitchandak]

Try like

Variance from Store A = [Price] - Maxx(filter('Table', 'Table'[Store] ="A"),[Price])

Or

Variance from Store A = [Price] - Maxx(filter(all('Table'), 'Table'[Store] ="A"),[Price])

[parry2k]

@Anonymous  always it will be compared against STORE A, solution depends on this answer.

[Anonymous]

--

[parry2k]

@Anonymous I knew it and that's why I asked.

You need to create a separate table for the slicer, with just one column, called Store, and put this on the slicer and now from here, it is super easy.

You can tweak it as per your model.

Compare = 
VAR __selectedStore = SELECTEDVALUE ( Slicer[Store] )
VAR __selectedStoreValue = CALCULATE  ( MAX ( Table[Price] ), Table[Store] = __selectedStore )
RETURN
MAX ( Table[Price] ) - __selectedStoreValue

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

@parry2k The requirements are for a static store "A"

The above response from the community member does not work. Do you have an alternate solution?

[parry2k]

@Anonymous hang tight going to send you the solution with dynamic and fixed store. 

[parry2k]

@Anonymous solution attached, both dynamic and fixed, in dynamic, there is treat for you, it highlights the selected store 🙂

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

@parry2k 

I was using the Variance from Store A (Fixed) solution, but it gives the same number for Price, and doesn't subtract from store "A". 

Do you know why it does this?

[parry2k]

@Anonymous not sure what you mean? Can you elaborate bit more with the example?

[Anonymous]

@parry2k 

I am using this measure to determine variance:

$ Sale Variance from Store A =

VAR __Store= "A"

VAR __SaleEach = CALCULATE(SUM('Table'[SumSale]), 'Table'[Store] = __Store)

RETURN

(SUM('Table[SumSale])- __SaleEach )

But I am still getting this result; the selected store shows $0 as expected, but the others are not subtracting from the selected store as thy should. exampls: $520.49 - $73.91