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
bsmcfaden
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

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

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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!

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!

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

 

@amitchandak 

 

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

Hi @bsmcfaden ,

 

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11 

 

The Card visual is calulated using the below.

ASLY.pngCalcs.png

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11 

 

Here's what I get:

Measure.png

 

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

Hi @bsmcfaden ,

 

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11 

 

Yes, thank you I did miss the SUM bracket.  The DAX measure does replace the current visual level filters, but does not resolve my original issue.

 

If I add a TEST date, how can I get this same card visual to reduce the quantity?

Hi @bsmcfaden ,

 

Are you adding TEST date as a slicer on the report?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11 

 

No slicers.  Here's a more complete picture.  As you can see, I'm trying to see where the units remaining to ship are in the process.  The DAX measure you provided does show the correct quantity in my WIP Assembly card, but when a TEST date is added to move the product to WIP Test, the WIP Assembly card does not decrement.

 

Visual.png

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.