Forum Discussion
VikcyD
1 year agoFrequent Visitor
Help Needed: DAX Logic to Count Sales Reps Meeting 2 Monthly Achievement Criteria
I have sales data and I'm trying to identify how many unique Sales Representatives qualify as 'winners'. A Sales Rep should be counted as a winner only if their achievement in the previous month was ...
- 1 year ago
Hi VikcyD
Please try this
Sales Ach_Status_Count = CALCULATE ( COUNTROWS ( FILTER ( VALUES ( SaleData[Terr_Code] ), VAR Terr = SaleData[Terr_Code] VAR CurrAch = CALCULATE ( DIVIDE ( [TotalSaleCYValue_INR], [Target_ValueCY_INR], 0 ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month] = MONTH ( MAX ( SaleData[Date] ) ) && 'Calendar'[Year] = YEAR ( MAX ( SaleData[Date] ) ) ), SaleData[Terr_Code] = Terr ) VAR PrevAch = CALCULATE ( DIVIDE ( [TotalSaleCYValue_INR], [Target_ValueCY_INR], 0 ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month] = MONTH ( EOMONTH ( MAX ( SaleData[Date] ), -1 ) ) && 'Calendar'[Year] = YEAR ( EOMONTH ( MAX ( SaleData[Date] ), -1 ) ) ), SaleData[Terr_Code] = Terr ) RETURN CurrAch >= 0.945 && PrevAch >= 0.995 ) ) )I hope this helps, please give a thumbs up and mark as solved if it does, thanks!
wardy912
Super User
1 year agoHi VikcyD
Please try this
Sales Ach_Status_Count =
CALCULATE (
COUNTROWS (
FILTER (
VALUES ( SaleData[Terr_Code] ),
VAR Terr = SaleData[Terr_Code]
VAR CurrAch =
CALCULATE (
DIVIDE ( [TotalSaleCYValue_INR], [Target_ValueCY_INR], 0 ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Month] = MONTH ( MAX ( SaleData[Date] ) )
&& 'Calendar'[Year] = YEAR ( MAX ( SaleData[Date] ) )
),
SaleData[Terr_Code] = Terr
)
VAR PrevAch =
CALCULATE (
DIVIDE ( [TotalSaleCYValue_INR], [Target_ValueCY_INR], 0 ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Month] = MONTH ( EOMONTH ( MAX ( SaleData[Date] ), -1 ) )
&& 'Calendar'[Year] = YEAR ( EOMONTH ( MAX ( SaleData[Date] ), -1 ) )
),
SaleData[Terr_Code] = Terr
)
RETURN
CurrAch >= 0.945 && PrevAch >= 0.995
)
)
)
I hope this helps, please give a thumbs up and mark as solved if it does, thanks!