Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.])) ) |
Thank you
Solved! Go to 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.
Hi,
Your question is not clear. Please show some sample data and the expected result on that data.
Eample mocked up in Excel
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?
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
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.
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.
Thanks
Scott
Hi,
You may download my PBI file from here.
Hope this helps.
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.
Hello Ashish
Please see the PBIX file below
I can opnly assume that this is due to blanks (which we wanted) in the result
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.
Thank you Winniz
This seems to have worked
Your assumption is correct - it is because of the blanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |