Forum Discussion

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

Help on Per Day and Month Calculation

Hello folks ,

 Please can you help me resolve the monthly count. The numbers are correct when i display the day, but if i remove the day, the monthly counts are incorrect. For the Data Model, the date on Calendar table is connected to Job_start

 

CntRepJob_StartJobID
23Steve10/1/2020J1234
15Mosaic10/1/2020J1235
19Perez10/1/2020J1236
14Mike10/1/2020J1237
4Greta10/1/2020J1238
21Mary10/1/2020J1239
20Kate10/1/2020J1240
20Jackie10/1/2020J1241
23Nancy10/1/2020J1242
18Angie10/1/2020J1243
20Steve10/2/2020J1244
19Mosaic10/2/2020J1245
1Perez10/2/2020J1246
14Diana10/2/2020J1247
10Mike10/2/2020J1248
1Greta10/2/2020J1249
19Mary10/2/2020J1250
22Kate10/2/2020J1251
18Jackie10/2/2020J1252
12Michele10/2/2020J1253
21Sam10/2/2020J1254
18Angie10/2/2020J1255
20Steve10/5/2020J1256
1Perez10/5/2020J1257
6Mike10/5/2020J1258
16Diana10/5/2020J1259
16Mike10/5/2020J1260
20Greta10/5/2020J1261
16Kate10/5/2020J1262
16Jackie10/5/2020J1263
3Evelyn10/5/2020J1264
12Michele10/5/2020J1265
13Nancy10/5/2020J1266
21Sam10/5/2020J1267
12Angie10/5/2020J1268
17Steve10/6/2020J1269
16Mosaic10/6/2020J1270
20Perez10/6/2020J1271
4Mike10/6/2020J1272
17Diana10/6/2020J1273
18Mary10/6/2020J1274
21Kate10/6/2020J1275
3Evelyn10/6/2020J1276
14Nancy10/6/2020J1277
21Sam10/6/2020J1278
19Mosaic10/7/2020J1279
18Perez10/7/2020J1280
16Diana10/7/2020J1281
13Mike10/7/2020J1282
17Greta10/7/2020J1283
17Mary10/7/2020J1284
21Jackie10/7/2020J1285
2Evelyn10/7/2020J1286
11Michele10/7/2020J1287
15Nancy10/7/2020J1288
20Sam10/7/2020J1289
11Angie10/7/2020J1290
18Steve10/8/2020J1291
19Mosaic10/8/2020J1292

 

The goal is to capture per day , how many reps were active and multiply it by a 5, but Steve, Kate and Angie get 8, 9 and 10 as the factor. 

 

Ex: on 10/1, there were 10 reps, so the target count for 10/1 should be 7*5 + (1*8  #for steve) + (1*9 #for kate) + (1*10 #for angie)

on 10/2 there were 12 reps, so the target count is 8*5 +  (1*8  #for steve) + (1*9 #for kate) + (1*10 #for angie) = 43

What I am trying to achieve is two visuals. 

Day Visual 

DateTarget Count
10/1/2062
10/2/2067
10/5/2072
10/6/2062
10/7/2082
10/8/2013

 

Monthly Visual

MonthTarget Count
Oct358

 

I'm able to achieve the Day breakdown, but when i try to display the month , i dont get the correct value. Please could you help me figure this out. 

 

 

Need Help

10 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    5 years ago

    If it is me, I would have done like this....

     

    a) Add calculated column in the transform data  

     #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom Goal", each if [Rep] = "Steve" then 8 else if [Rep] = "Kate" then 9 else if [Rep] = "Angie" then 10 else 5),

     

    gives something like 

     

    b) Dax Measure  

    Active Goal Count = 
var tempTable1 = SUMMARIZE('Your Table', 'Your Table'[Rep], 'Your Table'[Custom Goal], 'Your Table'[Job_Start])

Return sumx(tempTable1, 'Your Table'[Custom Goal])

     

    Output :

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    PBI5851 , You can create a measure like this and try

     

    sumx(table, Switch(true(), [Rep] = "Steve" , 8 , [Rep] = "Kate" , [Rep] = "Angie" , 10 , 5))

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

      Ashish_Mathur Thank you for the file. There is a mistake though. For May 10th, Mike shows up twice, but should be counted only once. so the count for May 10th will be 9 reps + steve + kate + angie i.e 45 +8+9+10 = 72. Any recommendation on how to fix that. 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

        Edit the measure to

        Measure = SUMX(SUMMARIZE(VALUES(Data[Rep]),[Rep],"ABCD",min(Data[Column])),[ABCD])

        Hope this helps.

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

    Hi, PBI5851 , I'd create a dimentional table for different factors respective to all rep as follows,

    Here's the data model 

    and the result

    You might want to refer to the attached file for details.

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

      CNENFRNL . The idea will work, but i dont think i can create a sub-table of drep, because the reps can change frequently and we can have new reps as its a moving list. Is there a way to populate the drep table dynamically.

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

        CNENFRNL I did try your solution too. But it doesnt work if there are multiple months. If its a single month, it works, but with multiple months, everything shows the same value.