Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
amitchandak
Super User
Super User

@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
Not applicable

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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)

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

I'm shooting in the dark right now.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.