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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Measure with sum divide and two filters

Hello , 

Please let me know how can I get the following in Power BI. I need a  sum/100 of calumn values that are Negative and from Quarter = Q2 FY21

 

It seem to be a simple task, but I cannot get it matched with my excel results.  The result is -1.33

 

Below is the example table:

 

  
QuarterValue
Q2 FY21    21
Q2 FY21   -43
Q2 FY21   -90
Q1FY21    23
Q4 FY21   -98
  
  
  
result -1.33
  
  

 

Many Thanks!!

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@Anonymous  I don't know what DAX code @amitchandak provided is going to do, it is not even close to what you asked for, and also it has syntax error. Try following measure:

 

Result = 
DIVIDE ( 
CALCULATE ( 
SUM ( Table[Value] ),
Table[Quarter] = "Q2 FY21",
Table[Value] < 0 
),
100 
)

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

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

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



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.

View solution in original post

 yes missed the filter

calculate(sum(Table[Value]), filter(Table[Quarter] ="Q2 FY21" && Table[Value] <0 ))/100

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@Anonymous You should tag regarding which solution you are referring too otherwise no idea what is not working or what you tried.



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.

parry2k
Super User
Super User

@amitchandak unfortunately still it is not going to work, take it easy man, and pay attention to the details. Just a piece of advice. Cheers!!



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.

parry2k
Super User
Super User

@Anonymous  I don't know what DAX code @amitchandak provided is going to do, it is not even close to what you asked for, and also it has syntax error. Try following measure:

 

Result = 
DIVIDE ( 
CALCULATE ( 
SUM ( Table[Value] ),
Table[Quarter] = "Q2 FY21",
Table[Value] < 0 
),
100 
)

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

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

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



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

Thank you!

amitchandak
Super User
Super User

@Anonymous ,  Try a measure like

calculate(sum(Table[Value]), filter(Table[Quarter] ="Q2 FY21"))/100

only negative values

 yes missed the filter

calculate(sum(Table[Value]), filter(Table[Quarter] ="Q2 FY21" && Table[Value] <0 ))/100

Anonymous
Not applicable

Thanks, this works.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.