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

SUM qty field, but decrement from sum when new variable added?

Need guidance!  I'm looking to sum a quantity column, but filter by 2 separate visuals.  The difficulty I am having is decrementing the quantity when an added variable is introduced.

 

I'm looking for the quantity sum in WIP using the ASLY & TEST fields as my current filters.  Below I can easily filter to get what quantity is in ASLY (qty 2 based on below), but when I add in a test date, the ASLY quantity doesn't decrement.  

 

I am unsure how to include a statement to decrement?  Thoughts?

 

QTYASLYTEST
113-Aug-20 
113-Aug-20 
2  
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @bsmcfaden :

 

Try the below dax expression:

measure=
var _countall=SUMX(FILTER(ALLSELECTED('Customer Orders'(3)),'Customer Orders'[ASLY]<>BLANK()),'Customer Orders'[Qty])
var _counttest==SUMX(FILTER(ALLSELECTED('Customer Orders'(3)),'Customer Orders'[ASLY]<>BLANK()&&'Customer Orders'[Test]<>BLANK()),'Customer Orders'[Qty])
Return
_countall-_counttest

If the above doesnt work,could you pls upload your .pbix file to onedrive business and share the link with us?

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

13 REPLIES 13
Highlighted
Super User II
Super User II

HI @bsmcfaden ,

 

Your requirement is not clear. Please detail it.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Thanks,

Pragati

 





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Highlighted

Hi @Pragati11 

 

Of course!   I am using a basic Card visual to calculate the sum of a quantity column (shown below):

ASLYASLY

 

 

 

 

This is calculated using two filters from an Excel file (fields shown below):

1.  ASLY column is NOT BLANK

2.  TEST column is BLANK

Excel.png

 

 

 

Challenge:  When a TEST date is added, the Card visual does not reduce/decrement the quantity.

Example:  The TEST date was added, but the Card visual did not get reduced by qty 1.

Excel1.pngASLY.png

 

 

 

 

Please let me know if this data is sufficient for support.  Thank you!

Highlighted

Hi @bsmcfaden ,

 

Can you please share the calculation that you did to populate value in your card visual?

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Highlighted

@Pragati11 

 

The Card visual is calulated using the below.

ASLY.pngCalcs.png

Highlighted

@bsmcfaden , Try

calculate(countrows(Table),isblank(Table[Test]))

 

Here the assumption is Asly is driving the date filter or joined with date table.

 

Also, refer how to join two dates with same date table. And Check open between dates.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Hi @bsmcfaden ,

 

Rather than putting visual level filters this way on your card visual, try creating a DAX measure below:

 

CalcMeasure = CALCULATE(SUM(tablename[Qty]),
FILTER(ALLSELECTED(tablename), tablename[ASLY] <> BLANK() && tablename[TEST] = BLANK())
)

 

Replace tablename in above expression with your table name.

 

Then move this measure to your card visual.

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Highlighted

@Pragati11 

 

Here's what I get:

Measure.png

 

Question:  is this measure just a replacement for the visual level filters?

Highlighted

@amitchandak 

 

Thank you.  I will review the options outlined in the link you provided.

Highlighted

Hi @bsmcfaden ,

 

If you see my suggested DAX expression clearly, you missed out to close a bracket after SUM function.

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors