cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Value Variance from filtered Row of column

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
 
help.PNG
 
Please help
9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: Value Variance from filtered Row of column

@ThePowerBIQueen , 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 )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted

Re: Value Variance from filtered Row of column

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

Highlighted
Community Support
Community Support

Re: Value Variance from filtered Row of column

HI @ThePowerBIQueen,

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

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Re: Value Variance from filtered Row of column

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

 

Store$ Sold$ Sold Variance from Store A<Expected Result
$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)
$2,045.77$2,046($20,209.41)
G$385.81$386($21,869.37)
H$823.68$824($21,431.50)
Highlighted
Super User IV
Super User IV

Re: Value Variance from filtered Row of column

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User IV
Super User IV

Re: Value Variance from filtered Row of column

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted

Re: Value Variance from filtered Row of column

No, it stopped working after I created an aggregation table using the data
Highlighted
Super User IV
Super User IV

Re: Value Variance from filtered Row of column

@ThePowerBIQueen 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User IV
Super User IV

Re: Value Variance from filtered Row of column

I'm shooting in the dark right now.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors