Calculate value for missing record (DAX)

[barjoman]

Hi everyone,

I have a problem which I'm hoping one of you will be able to assist me in solving. My dataset consists of hourly sales by sales rep. The relevant columns are:

ID (as Integer) <-- This is a unique identifier representing a sales shift. For a given rep on a given date, there should be one value (although it is possible that there are two or more)
Date (as Date), <-- This is the date the rep is working
Time (as 1 or 2 digit Integer), <-- This is the time of the last update
Rep (as Text), <-- This is the Unique Rep Identifier
Units (as Integer). <-- This is the number of units sold since the last update
Cum Units (as Integer), <-- This is the cumulative number of units sold so far for in the Sales Shift (represented by ID)

SampleData
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Rep[/TD]
[TD]Units[/TD]
[TD]Cum Units[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2016-12-12[/TD]
[TD]10[/TD]
[TD]123BA[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2016-12-12[/TD]
[TD]12[/TD]
[TD]123BA[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2016-12-12[/TD]
[TD]14[/TD]
[TD]123BA[/TD]
[TD]0[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2016-12-12[/TD]
[TD]16[/TD]
[TD]123BA[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2016-12-12[/TD]
[TD]10[/TD]
[TD]124ST[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2016-12-12[/TD]
[TD]12[/TD]
[TD]124ST[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2016-12-12[/TD]
[TD]14[/TD]
[TD]124ST[/TD]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


I have 2 separate, but related problems to solve:
1) I'm trying to build a table showing Total Units by Time. However, since Rep 124ST doesn't have a record for time 16, I'm not getting the correct output for that time.

Total Units = MAX(SampleData[Cum Units])

Desired Output:
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Total Units[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]

Current Output:
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Total Units[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

2) I'm trying to build a table which lists each rep, their Cum Units as of the most recent time (for all reps), and their Units for that time.

Latest Time = CALCULATE(MAX(SampleData[Time]),ALL(SampleData[Rep])) <--Correct
Increase Units = CALCULATE(SUM(SampleData[Units]),FILTER(SampleData,SampleData[Time]=[Latest Time])) <--Incorrect

Desired Output:
Latest Time = 16
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Rep[/TD]
[TD]Increase Units[/TD]
[TD]Total Units[/TD]
[/TR]
[TR]
[TD]123BA[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]124ST[/TD]
[TD]0[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]




The only solution that I can think of is to insert a record for 124ST at Time 16 with 0 Units and 8 Cum Units. But this is not necessarily feasible (data comes from a third party tool that would require development time to change) nor desirable (I don't want to increase data size with these records unless it is the only option).

Hope someone can come up with a solution to assist on both of these items.

Thanks,
Ben

 

[Bodders]

Ben

I'm just starting out with Power Pivot but had a similar-ish problem with blank rows not being reported. My suggestion is that you have 2 additional tables - one for the Reps (1 row per rep), and another one for Shifts which would also be 1 row per unique date/time. You then join those tables in the Diagram View to your main table, and create a Measure which uses IsBlank to force all combinations Rep and Shift to be included. I used this :
=if(isblank(sum(BaseData[KT])),0,sum(BaseData[KT]))
Hope that works for you
Bodders