Monthly comission calculation

[PBI5851]

Hi all,

 I am trying to do a monthly calculation (summing up every day's value) to calculate the comission for a rep . 

Date	Account Rep
9/1/20	steve
9/1/20	Austin
9/1/20	Mercer
9/1/20	Justin
9/2/20	Austin
9/2/20	Mercer
9/3/20	Steve
9/3/20	Mercer
9/3/20	Austin
9/3/20	Justin

For the above and a daily calculation i do a distinct count on the reps and multiple each rep with thier commission value ex: 9/1 - for steve it is 1*20 ,

for austin it is 1*15 etc

and then sum them all up the net-day commission. 

The problem is when i do monthly. Because the distinct is making the Sep count as 1*20 + 1*15 etc... And i am supposed to have it as adding up the commission per day for the entire month. 

* the reason i have the distinct is because steve can have multiple rows in the table for the same day due to an order being on hold or cancelled etc. The rule is, if they are on the table for that day, they get the commission. 

Any recommendation on how to get a monthly count instead of a daily count when displaying results on a monthly visual. 

[CNENFRNL]

Hi, @PBI5851 , as insufficient info on your data model was provided, I tweak one of my former projects which appears similar to your description.

The simplified data model is as follows,

Based on it, a matrix is created

You may want to refer to a dummy file for more details.

[PBI5851]

@CNENFRNL i agree i didnt provide clear information. I'll try explaining it a bit differently. 

CommDate	Name	OrdStatus	JobID
1/1/2010	Steve	Ship	J123
1/1/2010	Jake	Close	J124
1/1/2010	Monica	Close	J125
1/1/2010	Austin	Process	J126
1/1/2010	Steve	Close	J127
1/1/2010	Jake	Ship	J128
1/2/2010	Jake	Close	J129
1/2/2010	Jacob	Process	J130
1/2/2010	Steve	Close	J131
1/2/2010	Monica	Ship	J132
1/2/2010	Jake	Close	J133
2/2/2010	Amy	Ship	J134
2/2/2010	Amy	Close	J135
2/2/2010	Steve	Close	J136
2/2/2010	Austin	Close	J137
2/5/2010	John	Process	J138
2/5/2010	Austin	Process	J139
2/5/2010	Steve	Close	J140
2/5/2010	Mary	Ship	J141
2/5/2010	Jake	Close	J142
2/5/2010	Jake	Ship	J143

The below info is inidividual's criteria, not part of any table

Amy	2
Monica	2
Steve	5
Jacob	2
Mary	5

So if the above person has an order for a day, their target is to make sure it is 1* the above number. If they are active for that day, it is 1*the above number, irrespective of how many orders are placed. Using above data, i need to provide two visuals, a Day and a Monthly visual. Default being 3 i.e for Austin and other it is 1*3

Day Visual

Day	OrdersPlaced	Target
1/1/2010	6	13
1/2/2010	5	12
2/1/2010	4	10
2/5/2010	6	10

Monthly Visual

Year	Month	OrderPlaced	target
2010	Jan	11	25
2010	Feb	10	20

To explain the Target for 1/1/10. Total Orders placed were 6. But the individuals who placed an Order on 1/1 were Austin (1*3), Jake (1*3), Monica (1*2) and Steve (1*5), which brings my Target to 13.

For Data Model - I have a date table connected to Job via the Date - CommDate

Hope this helps.  I am getting the Day Visual , but the Monthly visual is incorrect. 

TMOrderPlaced = CALCULATE(COUNTA(CommDetail[JobID]), USERELATIONSHIP(Datetable[Date], CommDetail[CommDate])))

TMOrderTarget = 

var __Amy = CALCULATE(COUNTROWS(CommDetail),FILTER(CommDetail, CommDetail[Name] = "Amy"), USERELATIONSHIP(Datetable[Date], CommDetaill[CommDate]))

var __Monica = CALCULATE(COUNTROWS(CommDetail),FILTER(CommDetail, CommDetail[Name] = "Monica"), USERELATIONSHIP(Datetable[Date], CommDetaill[CommDate]))

var __Steve = CALCULATE(COUNTROWS(CommDetail),FILTER(CommDetail, CommDetail[Name] = "Steve"), USERELATIONSHIP(Datetable[Date], CommDetaill[CommDate]))

var __Jacob = CALCULATE(COUNTROWS(CommDetail),FILTER(CommDetail, CommDetail[Name] = "Jacob"), USERELATIONSHIP(Datetable[Date], CommDetaill[CommDate]))

var __Mary = CALCULATE(COUNTROWS(CommDetail),FILTER(CommDetail, CommDetail[Name] = "Mary"), USERELATIONSHIP(Datetable[Date], CommDetaill[CommDate]))

var __Other = CALCULATE(DISTINCTCOUNT(CommDetail[Name]),FILTER(CommDetail, not CommDetail[Name] in {"Amy","Monica","Steve","Jacob","Mary"} ,USERELATIONSHIP(Datetable[Date], CommDetail[CommDate]))

Return

DIVIDE(__Amy, __Amy,0) * 2 + DIVIDE(__Monica, __Monica,0) * 2+ DIVIDE(__Steve, __Steve, 0)*5 + DIVIDE(__Jacob,__Jacob,0) *2 + DIVIDE(__Mary,__Mary,0) *5+  __Other * 3

[v-shex-msft · Accepted solution]

Hi @PBI5851,

So you mean the target is the record count based on date and person(not consider the order status) and the real amount is 'unclosed' records count and multiply based on the corresponding person's rate, right?

If this is a case, you can try to use the following measure formulas:

Plan = 
CALCULATE (
    COUNT ( T1[JobID] ),
    ALLSELECTED ( T1 ),
    VALUES ( T1[CommDate] ),
    VALUES ( T1[Name] )
)

Real = 
VAR summary =
    SUMMARIZE (
        T1,
        [CommDate],
        [Name],
        "Real",
            CALCULATE (
                COUNT ( T1[JobID] ),
                FILTER ( ALLSELECTED ( T1 ), [OrdStatus] <> "Close" ),
                VALUES ( T1[CommDate] ),
                VALUES ( T1[Name] )
            )
                * LOOKUPVALUE ( Person[Rate], Person[Person], T1[Name] )
    )
RETURN
    SUMX ( summary, [Real] + 0 )

Regards,

Xiaoxin Sheng