Forum Discussion
Line Item Calculation at Header Level / OTIF Score Rolled up
Hello,
I am looking for help with a measure I am trying to solve in Power BI, basic Excel example below of the desired result (yellow).
Dataset example in blue.
The goal is to be able to view the original OTIF score at line level but, when filtering on that line, the header level score should reflect 0
I have searched and tried various examples in the forum but can't quite get the result I am looking for.
Thanks!
Hi, Nph
Thank you very much for your reply. What you mean is that if only one of the OTIF Score at Line Item Level corresponding to the current Order number is 0, then the OTIF Score at Order Header Level of the Order number is 0. Take the data you provided, if one of the 1234 is 0, then the OTIF Score at Order Header Level corresponding to 1234 is 0?
You can try the following DAX expressions:
MEASURE = VAR _seleted = SELECTEDVALUE ( 'Table'[Order Number] ) VAR _table = SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[OTIF Score at Line Item Level] = 0 ), 'Table'[Order Number] ) RETURN IF ( _seleted IN _table, 0, 1 )Here are the results:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi, Nph
Based on the data provided by your image and the expected output, my sample data is as follows:
I use the following DAX expression to get the results you expect:
OTIF Score at Order Header Level = VAR _currentline_order_number = SELECTEDVALUE ( 'Table'[Order Number] ) VAR _currentline_OTIF = SELECTEDVALUE ( 'Table'[OTIF Score at Line Item Level] ) RETURN SWITCH ( TRUE (), _currentline_OTIF = 1, IF ( _currentline_order_number = 1234, 0, 1 ), _currentline_OTIF = 0, 0 )Here are the results:
I've provided the PBIX file used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for taking the time to reply on this.
In this part of the measure,IF ( _currentline_order_number = 1234, 0, 1 ),
I would need to reference the sales order column rather than a specific sales order #, what is the best way to do this?
Hi, Nph
Thank you very much for your reply. If you're citing a column, then what are the judging criteria. Is it determined that the order number=1234 of the previous row determines whether it is 0 or 1?
Best Regards
Jianpeng Li
hi
erm I am not sure I follow your question.
The criteria is, if any line within the order = 0 then each line should also = 0.Hi, Nph
Thank you very much for your reply. What you mean is that if only one of the OTIF Score at Line Item Level corresponding to the current Order number is 0, then the OTIF Score at Order Header Level of the Order number is 0. Take the data you provided, if one of the 1234 is 0, then the OTIF Score at Order Header Level corresponding to 1234 is 0?
You can try the following DAX expressions:
MEASURE = VAR _seleted = SELECTEDVALUE ( 'Table'[Order Number] ) VAR _table = SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[OTIF Score at Line Item Level] = 0 ), 'Table'[Order Number] ) RETURN IF ( _seleted IN _table, 0, 1 )Here are the results:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, that worked!
