topic Re: Count of invoices below threshold DAX in DAX Commands and Tips

Count of invoices below threshold DAX

adsam — Wed, 29 Jan 2025 14:16:38 GMT

Hi,

I m trying to create a measure to count number of invoices below threshold, threshold table is defined as below.

my measure is working somehow but not giving total number of invoices, there is something wrong.

kindly need assistance.

Invoice Count Below threshold = VAR CurrentInvoiceAmount = [Inv. Amt SAR] VAR ThresholdValue = CALCULATE( MAX(Matrix_Invoice_Value[Value]), TREATAS( VALUES(Route_Master[Country]), Matrix_Invoice_Value[Country] ), TREATAS( VALUES(Customer_Master[Trade Category]), Matrix_Invoice_Value[Customer Type] ), TREATAS( VALUES(Route_Master[Route Type3]), Matrix_Invoice_Value[Route Type3] ), TREATAS( VALUES(Route_Cat[Attribute]), Matrix_Invoice_Value[Attribute] ) ) RETURN CALCULATE( [Invoice Count], FILTER( Daily_invioces, AND( NOT (ISBLANK(ThresholdValue)), CurrentInvoiceAmount < ThresholdValue ) ) )

threshold table

Measure result:

Data model

Re: Count of invoices below threshold DAX

Greg_Deckler — Wed, 29 Jan 2025 14:45:58 GMT

adsam First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Count of invoices below threshold DAX

adsam — Thu, 30 Jan 2025 05:49:38 GMT

it didn't solve my problem, I think we need to use iterator function, which will scan all "daily invoice" table and count invoices which are below criteria mentioned in threshold table. Please help.

Re: Count of invoices below threshold DAX

adsam — Tue, 04 Feb 2025 06:53:19 GMT

I was able to get desired result from below DAX:

Invoice Count Below threshold = SUMX( Daily_invioces, VAR CurrentInvoiceAmount = [Inv. Amt SAR] VAR ThresholdValue = CALCULATE( MAX(Matrix_Invoice_Value[Value]), TREATAS( VALUES(Route_Master[Country]), Matrix_Invoice_Value[Country] ), TREATAS( VALUES(Customer_Master[Trade Category]), Matrix_Invoice_Value[Customer Type] ), TREATAS( VALUES(Route_Master[Route Type3]), Matrix_Invoice_Value[Route Type3] ), TREATAS( VALUES(Route_Cat[Attribute]), Matrix_Invoice_Value[Attribute] ) ) RETURN IF(AND( NOT (ISBLANK(ThresholdValue)), CurrentInvoiceAmount < ThresholdValue ),1 ) )