DistinctCount Per specific conditions

[Anonymous]

Hi Everyone,

I have an issue with the correct total of a measure:

I"m trying to distinct count the # of Labs (customers) that generate more than $650 in the last half a year.

As you see below, I'm filtered on Date Hierarchy for the last 6 months but the Count if Labs above $650 (1) or the Count if Labs above $650(2) do not sum the correct total per this group of customers (redacted the name). Two attempts which were both unsuccessful

Count if Labs above $650 (1):=

if(calculate(Sum(SALES[Rev After XX]),SALES[Division2]="Labs")>650,calculate(DISTINCTCOUNT(SALES[SCC]),keepfilters('Calendar'[Date])),blank())

Count if Labs above $650 (2):=

if((calculate(Sum(SALES[Rev After XX]),SALES[Division2]="Labs")/calculate(DISTINCTCOUNT(SALES[SCC]),SALES[Division2]="Labs"))>650,calculate(DISTINCTCOUNT(SALES[SCC]),keepfilters('Calendar'[Date])),blank())

You can see in the Revenue Compliant measure I am able to get only the totals for Labs (customers) that are compliant with the 650 rule (vs Revenue All which takes all instances).

I'm for some reason not able to get either of distinct count measures to sum to 9...

Would very much appreciate your support!

Thanks

Eyal

[Anonymous]

@littlemojopuppy  i will check it out and and add as a solution as well shortly. Thanks!

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

This will give you the count of labs with at least 650 in revenue.

VAR	LabSummary =
	ADDCOLUMNS(
		Labs,
		"SixMonthsRevenue",
		CALCULATE(
			[Revenue]
			DATESINPERIOD(
				DateTable[Date],
				MAX(DateTable[Date]),
				-6,
				MONTH
			)
		)
	)
RETURN

COUNTROWS(
	FILTER(
		LabSummary,
		SixMonthsRevenue >= 650
	)
)

Fair warning: I typed this in Notepad so might not be syntactically correct.  Hope this helps.

[tamerj1]

Hi @Anonymous 

Try

New Measure =
SUMX (

SUMMARIZE ( Sales, Sales[BUYING GROUP], Sales[SCC] ),

[Old Measure]
)