I have a table that looks like this:
Order # | Date requested by | Actual delivery date | item ID | Qty ordered | Qty delivered | Result |
54386 | 5/4/2020 | 5/7/2020 | 238 | 347 | 347 | Late |
54386 | 4/17/2020 | 4/16/2020 | 192 | 220 | 220 | On time |
79345 | 6/3/2020 | 6/1/2020 | 681 | 15 | 15 | On time |
79345 | 6/17/2020 | 6/5/2020 | 356 | 110 | 110 | On time |
40321 | 2/17/2020 | 2/21/2020 | 974 | 56 | 56 | Late |
40321 | 3/1/2020 | 2/13/2020 | 606 | 89 | 89 | On time |
40321 | 4/12/2020 | 4/9/2020 | 135 | 200 | 200 | On time |
The "Result" column is a calculated field that determines whether the item on an order was on time or late. In most cases, an order will have multiple items on it and each item may have different delivery dates requested by the customer. I want to set up an additional column/measure that looks at the order as a whole and if one item on that order was late, then the whole order would be classified as late. Below is my desired result. What would the formula look like for that? Any help would be appreciated!
Order # | Overall Result |
54386 | Late |
79345 | On Time |
40321 | Late |
Solved! Go to Solution.
Hi @jakeudy ,
I think you are talking about this:
Column =
VAR x =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Order #] ),
'Table'[Actual delivery date] > 'Table'[Date requested by]
)
)
RETURN
IF ( x > 0, "Late", "On time" )
Measure =
VAR x =
COUNTROWS (
FILTER ( 'Table', 'Table'[Actual delivery date] > 'Table'[Date requested by] )
)
RETURN
IF ( x > 0, "Late", "On time" )
For more details, please see the attachment.
@jakeudy , Try a new measure like
maxx(summarize(Table,Table[order], "_1", calculate(countrows(Table),Table[Result]= "Late")),if([_1]>=1,"Late","On-Time"))
This sort of worked, however its not quite what I'm looking for. When I view the data in table format it works correctly. However, when I try to view it in a different format such as a card or a column chart, it messes up the calculation. Ideally I would like to use this measure to see what percentage of orders are on time and what percentage are late. So I'd like to build a 100% stacked column chart with this measure as the legend, and the count (distinct) of orders as the value. Is there something I can add to the formula to accomodate this? Do I need to change it from a measure to a column? I've noticed that using a measure in the legend of a visual often does not work. Thank you for your help! @amitchandak
Hi @jakeudy ,
I think you are talking about this:
Column =
VAR x =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Order #] ),
'Table'[Actual delivery date] > 'Table'[Date requested by]
)
)
RETURN
IF ( x > 0, "Late", "On time" )
Measure =
VAR x =
COUNTROWS (
FILTER ( 'Table', 'Table'[Actual delivery date] > 'Table'[Date requested by] )
)
RETURN
IF ( x > 0, "Late", "On time" )
For more details, please see the attachment.
This worked perfectly, thank you!
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
107 | |
75 | |
66 | |
50 | |
48 |
User | Count |
---|---|
163 | |
85 | |
76 | |
68 | |
67 |