cumulative count measure per row

lawadaa — Wed, 20 Dec 2023 07:28:37 GMT

hello

i need to create a measure that calculates incentive as per below rules

Achievement	Incentive
1 to 5 count of sales	2$
6 to 10 count of sales	4 $
11 to 15 count of sales	6 $
16 and above count of sales sales	8 $

what this means, if agent has made 15 transactions, he will get incentive of (2$*5) for first 5 transactions, and (4$*5) for next 5 transactions and (6$ *5) the 5 transactions after. so total incentive= 10+20+30=60$

i created below measure

= CALCULATE(IF([MNP cumulative count]<=5,[MNP cumulative count]*2,IF([MNP cumulative count]>=6 &&[MNP cumulative count]<=10,[MNP cumulative count]*4,IF([MNP cumulative count]>=11 &&[MNP cumulative count]<=15,[MNP cumulative count]*6,IF([MNP cumulative count]>=16,[MNP cumulative count]*8,0)))))

and [MNP cumulative count]=

CALCULATE(SUMX(SUMMARIZE('categories trans','categories trans'[AGENT_ID],'categories trans'[AGENT_NAME],'categories trans'[TRANSDATE],'categories trans'[achievement],'categories trans'[Categoty]),[mnp distinct count]), FILTER (

ALLSELECTED('categories trans'), 'categories trans'[TRANSDATE]<=MAX('categories trans'[TRANSDATE])))

my problem is the cumulitve count does not count correctly when agent has 2 transactions in the same day. instead of counting them as 2 it's counting them as 1 also

the data i have is as below table

agent id	agent name	transaction value (achievement)	date
123	abc	2	1/11/2023
456	def	1	2/11/2023

Re: cumulative count measure per row

lbendlin — Sat, 23 Dec 2023 23:19:14 GMT

Please provide sample data that fully covers your issue. That means sales reps with different scenarios.
Please show the expected outcome based on the sample data you provided.

Re: cumulative count measure per row

sjoerdvn — Thu, 28 Dec 2023 15:10:36 GMT

Try something like below. Unless that transaction date has an active relationship with a date table and you're using that in a slicer or filter, in that case use the date from your date table.

[MNP cumulative count]= CALCULATE(COUNTROWS('categories trans'),ALL('categories trans'[TRANSDATE]),'categories trans'[TRANSDATE]<=MAX('categories trans'[TRANSDATE]))

topic Re: cumulative count measure per row in DAX Commands and Tips

cumulative count measure per row

Re: cumulative count measure per row

Re: cumulative count measure per row