Value Variance from filtered Row of column

[Anonymous]

I am using this measure to determine variance:

$ Sale Variance from Fisher =

VAR __Store= "Fisher"

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

Please help

[amitchandak]

@Anonymous , try like

$ Sale Variance from Fisher =
VAR __Store= "Fisher"
VAR __SaleEach = CALCULATE(SUM('Table'[SumSale]),filter(all('Table'), 'Table'[Store] = __Store))
RETURN
(SUM('Table[SumSale])- __SaleEach )

[Anonymous]

I am still not getting the expected result 😞 ..any other suggestion?

[v-shex-msft]

HI @Anonymous,

Please provide some dummy data to test, it will help for us to test and modify Dax formulas.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

See attached for sample data file. @v-shex-msft @amitchandak @parry2k  

Store	$ Sold	$ Sold Variance from Store A	<Expected Result
A	$22,255.18	$0	$0
B	$6,643.79	$6,644	($15,611.39)
C	$2,183.03	$2,183	($20,072.15)
D	$1,749.00	$1,749	($20,506.18)
E	$2,693.52	$2,694	($19,561.66)
F	$2,045.77	$2,046	($20,209.41)
G	$385.81	$386	($21,869.37)
H	$823.68	$824	($21,431.50)

[amitchandak]

@Anonymous , please find the attached solution. There was a space in A I removed. Not sure it is there in your actual data

[parry2k]

@Anonymous Isn't the solution I provided on another post works? What are we missing in that solution? Here is the link to another post.

[Anonymous]

No, it stopped working after I created an aggregation table using the data

[parry2k]

@Anonymous still not very clear what stopped working in my original solution. If you can be more specific, provide the sample pbix file with my solution and let me know what is not working, it will help to get you back with the answer.

[parry2k]

I'm shooting in the dark right now.