Forum Discussion
Variance Column based on a row with conditions
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 |
10 Replies
- parry2k
Super User
Anonymous always it will be compared against STORE A, solution depends on this answer.
- 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] ) - __selectedStoreValueI 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!
- 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])
