# Calculate value for missing record (DAX)

#### barjoman

##### Board Regular
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
 ID Date Time Rep Units Cum Units 1 2016-12-12 10 123BA 1 1 1 2016-12-12 12 123BA 3 4 1 2016-12-12 14 123BA 0 4 1 2016-12-12 16 123BA 2 6 2 2016-12-12 10 124ST 2 2 2 2016-12-12 12 124ST 3 5 2 2016-12-12 14 124ST 3 8

<tbody>
</tbody>

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:
 Time Total Units 10 3 12 9 14 12 16 14

<tbody>
</tbody>

Current Output:
 Time Total Units 10 3 12 9 14 12 16 6

<tbody>
</tbody>

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
 Rep Increase Units Total Units 123BA 2 6 124ST 0 8

<tbody>
</tbody>

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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Bodders

##### New Member
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

Replies
1
Views
166
Replies
0
Views
157
Replies
0
Views
88
Replies
1
Views
216
Replies
1
Views
320

1,190,836
Messages
5,983,174
Members
439,825
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back