cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ScottE
Frequent Visitor

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

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 

 

ScottE_0-1615165602141.png

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

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @ScottE ,

Add a measure:

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

v-kkf-msft_0-1615457262147.png

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.

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Eample mocked up in Excel

 

OTIF example.JPG

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

ScottE_1-1615248618438.png

 

Cheers

Scott

 

 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Community Support
Community Support

Hi @ScottE ,

Add a measure:

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

v-kkf-msft_0-1615457262147.png

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.

View solution in original post

Thank you Winniz

This seems to have worked

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!