Help on Per Day and Month Calculation

[PBI5851]

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

Cnt	Rep	Job_Start	JobID
23	Steve	10/1/2020	J1234
15	Mosaic	10/1/2020	J1235
19	Perez	10/1/2020	J1236
14	Mike	10/1/2020	J1237
4	Greta	10/1/2020	J1238
21	Mary	10/1/2020	J1239
20	Kate	10/1/2020	J1240
20	Jackie	10/1/2020	J1241
23	Nancy	10/1/2020	J1242
18	Angie	10/1/2020	J1243
20	Steve	10/2/2020	J1244
19	Mosaic	10/2/2020	J1245
1	Perez	10/2/2020	J1246
14	Diana	10/2/2020	J1247
10	Mike	10/2/2020	J1248
1	Greta	10/2/2020	J1249
19	Mary	10/2/2020	J1250
22	Kate	10/2/2020	J1251
18	Jackie	10/2/2020	J1252
12	Michele	10/2/2020	J1253
21	Sam	10/2/2020	J1254
18	Angie	10/2/2020	J1255
20	Steve	10/5/2020	J1256
1	Perez	10/5/2020	J1257
6	Mike	10/5/2020	J1258
16	Diana	10/5/2020	J1259
16	Mike	10/5/2020	J1260
20	Greta	10/5/2020	J1261
16	Kate	10/5/2020	J1262
16	Jackie	10/5/2020	J1263
3	Evelyn	10/5/2020	J1264
12	Michele	10/5/2020	J1265
13	Nancy	10/5/2020	J1266
21	Sam	10/5/2020	J1267
12	Angie	10/5/2020	J1268
17	Steve	10/6/2020	J1269
16	Mosaic	10/6/2020	J1270
20	Perez	10/6/2020	J1271
4	Mike	10/6/2020	J1272
17	Diana	10/6/2020	J1273
18	Mary	10/6/2020	J1274
21	Kate	10/6/2020	J1275
3	Evelyn	10/6/2020	J1276
14	Nancy	10/6/2020	J1277
21	Sam	10/6/2020	J1278
19	Mosaic	10/7/2020	J1279
18	Perez	10/7/2020	J1280
16	Diana	10/7/2020	J1281
13	Mike	10/7/2020	J1282
17	Greta	10/7/2020	J1283
17	Mary	10/7/2020	J1284
21	Jackie	10/7/2020	J1285
2	Evelyn	10/7/2020	J1286
11	Michele	10/7/2020	J1287
15	Nancy	10/7/2020	J1288
20	Sam	10/7/2020	J1289
11	Angie	10/7/2020	J1290
18	Steve	10/8/2020	J1291
19	Mosaic	10/8/2020	J1292

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 

Date	Target Count
10/1/20	62
10/2/20	67
10/5/20	72
10/6/20	62
10/7/20	82
10/8/20	13

Monthly Visual

Month	Target Count
Oct	358

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. 

[CNENFRNL]

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]

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

@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. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[PBI5851]

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

Hi,

Edit the measure to

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

Hope this helps.

[PBI5851]

Hi @Ashish_Mathur . As per your recommendation (named as New Measure) , the individual values are correct, but the total is now incorrect.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@PBI5851 , You can create a measure like this and try

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

[sevenhills]

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 :