Count of invoices below threshold DAX

[adsam]

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. 

Filelink

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

Threshold Table Min invoice target value

Measure result:

Result of my measure

Data model

Data Model

[adsam · Accepted solution]

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

[adsam]

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.

[Greg_Deckler]

@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