Forum Discussion

lawadaa's avatar
lawadaa
Icon for Helper I rankHelper I
2 years ago

cumulative count measure per row

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 idagent nametransaction value  (achievement)date
123abc21/11/2023
456def12/11/2023
" cumulative "
"COUNT"
"DAX help"
"DAX"
CUMULATIVE COUNT
Cumulative Total
DAX Calculation

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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.

  • sjoerdvn's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    2 years ago

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