count of sales reps under target

[VV24]

all,

Im having troubles with a measure determining the amount of sales reps that are under target for a specific selection in a certain period.

I have a fact table sales and a fact table targets, both connected by the sales rep dimension table and the datetable, I wish to count the amount of Sales reps that are under target in the selected period, or other filters (that apply to both fact tables).

is there a way to calculate this?

[m3tr01d]

You probably have two base measures like this :

Target_Amount = SUM( Target[Amount] )
Sales_Amount  = SUM( Sales[Amount] )

 
Now what you can do is loop over each Rep and for each, calculate the two measures and only count those where
[Target_Amount] < [Sales_Amount]

Count_Rep_No_Target = 
COUNTROWS(
	FILTER(
		SalesRep,
		[Target_Amount] < [Sales_Amount]
	)
)

Tell me if it works 😛

[VV24]

seems to work, but the total is not working because on total it is above target, so the total is 0. I want to put this in a kpi card so then the count is not properly displayed

[m3tr01d]

Can you share with me a visual where the measure works and the one that don't works 

[VV24]

You see my page here:

i already created a blank correction in my measure by doing this:

Salesreps under target (blank correction) =

var calc = COUNTROWS(FILTER(dimSalesRep,SUM(Sales[Amount]) < SUM(Target[Amount])))

var calc2 = IF(ISBLANK(calc),0,calc)

return

calc2

but i want the total to work as well and use that in my my kpi card

[PaulDBrown · Accepted solution]

Try:

Sales rep under target =
SUMX (
dimSalesRep,
COUNTROWS (
FILTER ( dimSalesRep, SUM ( Sales[Amount] ) < SUM ( Target[Amount] ) )
)
)

or

Sales rep under target =
COUNTX (
    FILTER ( dimSalesRep, SUM ( Sales[Amount] ) < SUM ( Target[Amount] ) ),
    dimSalesRep[Sales rep]
)

[m3tr01d]

Ha I see,
You'll need to use the two measures inside the FILTER statement. 
Using SUM( ... ) won't work as It won't trigger Context transition for each Sales Rep.
Create 2 measues (One for target and one for Sales like I did)

[m3tr01d]

So you want to count the number of Sales Rep that did not achieve their target?

[VV24]

correct!