Calculate for the first record of a sales order in a matrix

[ScottE]

Hello I am attempting to work out Orders in full on time by line.

I have a calculation by Sales order based on a group of customer reference numbers Customer Ref (IE total lines on order) and the number of lines shipped on each order Order Count for the same Customer Ref. 

For OTIF by Line I want to do the same calculation on the first Customer Ref line only, basically the first sales order for the group of Customer Ref. As I do not want the second (or other) entries to lower the total percentage. 

This is graphed by Month Year.

Sample data 

Dim_OIFT_Count is a referenced table of Fact_OTIF with duplicates taken out for Customer Ref.

Customer Order_Count =

SUMX(

VALUES('Customer Orders'[Customer Ref]),

CALCULATE(COUNT('Fact_OIFT'[Order No.]))

)

Cust RNum Count =

SUM(Dim_OIFT_Count[Fact2_Count])

Thank you

[Ashish_Mathur]

Hi,

Your question is not clear.  Please show some sample data and the expected result on that data.

[ScottE]

Eample mocked up in Excel

[Ashish_Mathur]

Hi,

In the rows that you have highlighed in green, let's take the example of Customer ref 2130.  How does one decide which of the two rows should be left blank in the OTIF by row column?

[ScottE]

Hello Ashish

This is a good point!

Looking at the raw data there is another date-time fileld that has a different date/time than the Order date which all entries have the same date/time 😞

Can we do some form of calculation on this date/time to find the first entry of the Customer Ref?

Cheers

Scott

[Ashish_Mathur]

Hi,

We can try.  Share the download link of that workbook.  Also, in the Completed date column, i will be ignoring the time stamp.  I'll consider only the date portion.  Hope that should be fine.

[ScottE]

Hello Ashish

Please see the link to the sheet below.

I think that using date alone will be fine this will also keep my dates the same format in Bi.

Sample file 2 

Thanks 

Scott

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ScottE]

HI Ashish

That is excellent! Many thanks for your help!

Forgive my lack of DAX knowledge but I am confused as to why there is no total for OTIF by Line as I was attempting to place this in a line chart by month.

Cheers

Scott

[Ashish_Mathur]

You are welcome.  Share the PBI file - show me the problem.

[ScottE]

Hello Ashish

Please see the PBIX file below

I can opnly assume that this is due to blanks (which we wanted) in the result 

Link to PBIX file 

[v-kkf-msft · Accepted solution]

Hi @ScottE ,

Add a measure:

Measure = 
IF(
    HASONEFILTER(Data[Order No.]),
    [OTIF by line],
    AVERAGEX(
        VALUES(Data),
        [OTIF by line]
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ScottE]

Thank you Winniz

This seems to have worked

[Ashish_Mathur]

Your assumption is correct - it is because of the blanks.