Forum Discussion

PBI5851's avatar
PBI5851
Icon for Helper V rankHelper V
5 years ago
Solved

Monthly comission calculation

Hi all,

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

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

 

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      PBI5851
      Icon for Helper V rankHelper V
      5 years ago

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

      CommDateNameOrdStatusJobID
      1/1/2010SteveShipJ123
      1/1/2010JakeCloseJ124
      1/1/2010MonicaCloseJ125
      1/1/2010AustinProcessJ126
      1/1/2010SteveCloseJ127
      1/1/2010JakeShipJ128
      1/2/2010JakeCloseJ129
      1/2/2010JacobProcessJ130
      1/2/2010SteveCloseJ131
      1/2/2010MonicaShipJ132
      1/2/2010JakeCloseJ133
      2/2/2010AmyShipJ134
      2/2/2010AmyCloseJ135
      2/2/2010SteveCloseJ136
      2/2/2010AustinCloseJ137
      2/5/2010JohnProcessJ138
      2/5/2010AustinProcessJ139
      2/5/2010SteveCloseJ140
      2/5/2010MaryShipJ141
      2/5/2010JakeCloseJ142
      2/5/2010JakeShipJ143

       

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

      Amy2
      Monica2
      Steve5
      Jacob2
      Mary5

       

      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

      DayOrdersPlacedTarget
      1/1/2010613
      1/2/2010512
      2/1/2010410
      2/5/2010610

       

      Monthly Visual

      YearMonthOrderPlacedtarget
      2010Jan1125
      2010Feb1020

       

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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