Forum Discussion
Counting orders based on their latest status
Hello,
For some time now I am strugling with measure that will count orders based on their last status.
I tackled this from multiple angles and even ask ChatGPT for help, but couldn't find a solution.
My goal is table visualization with columns for:
names of statuses in first column (STATUS_DICTIONARY[NAME]),
number of orders which have given status as their latest,
% of GT
My model looks like this:
ORDERS[ID] - 1 to * - ORDERS_STATUS[ID_ORDER]
STATUS_DICTIONARY[ID] - 1 to * - ORDERS_STATUS[ID_STATUS]
And below is some sample data:
ORDERS
| ID | NUMBER |
| 1 | 23/01/1 |
| 2 | 23/01/2 |
| 3 | 23/01/3 |
| 4 | 23/01/4 |
| 5 | 23/01/5 |
ORDERS_STATUS
| ID | ID_ORDER | ID_STATUS | DATE_CHANGE |
| 1 | 1 | 1 | 26.09.2023 |
| 2 | 1 | 2 | 26.09.2023 |
| 3 | 2 | 1 | 26.09.2023 |
| 4 | 3 | 1 | 27.09.2023 |
| 5 | 4 | 1 | 27.09.2023 |
| 6 | 1 | 3 | 27.09.2023 |
| 7 | 2 | 5 | 28.09.2023 |
| 8 | 3 | 2 | 28.09.2023 |
| 9 | 3 | 3 | 29.09.2023 |
| 10 | 5 | 1 | 30.09.2023 |
| 11 | 4 | 2 | 30.09.2023 |
STATUS_DICTIONARY
| ID | NAME |
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
| 5 | eee |
Based on all above I expect result to look like this:
| STATUS NAME | NUMBER OF ORDERS | % OF GT |
| aaa | 1 | 20% |
| bbb | 1 | 20% |
| ccc | 2 | 40% |
| ddd | 0 | 0% |
| eee | 1 | 20% |
I will be very gratefull for any help.
Thank you!
Maciej1992 , here are the measures:
orders_amt = VAR t = FILTER ( ORDERS_STATUS, VAR order_id = CALCULATE ( SELECTEDVALUE ( ORDERS[ID] ) ) VAR maxDt = CALCULATE ( MAX ( ORDERS_STATUS[DATE_CHANGE] ), ORDERS_STATUS[ID_ORDER] = order_id, ALL ( ORDERS_STATUS ) ) VAR maxStatus = CALCULATE ( MAX ( ORDERS_STATUS[ID_STATUS] ), ORDERS_STATUS[DATE_CHANGE] = maxDt, ORDERS_STATUS[ID_ORDER] = order_id ) RETURN ORDERS_STATUS[ID_ORDER] = order_id && ORDERS_STATUS[ID_STATUS] = maxStatus ) RETURN COUNTROWS ( t )% OF GT = VAR latestRows = [orders_amt] VAR allrows = CALCULATE ( SUMX ( VALUES ( STATUS_DICTIONARY[NAME] ), [orders_amt] ), ALL ( STATUS_DICTIONARY[NAME] ) ) RETURN latestRows / allrows
2 Replies
- ERD
Community Champion
Maciej1992 , here are the measures:
orders_amt = VAR t = FILTER ( ORDERS_STATUS, VAR order_id = CALCULATE ( SELECTEDVALUE ( ORDERS[ID] ) ) VAR maxDt = CALCULATE ( MAX ( ORDERS_STATUS[DATE_CHANGE] ), ORDERS_STATUS[ID_ORDER] = order_id, ALL ( ORDERS_STATUS ) ) VAR maxStatus = CALCULATE ( MAX ( ORDERS_STATUS[ID_STATUS] ), ORDERS_STATUS[DATE_CHANGE] = maxDt, ORDERS_STATUS[ID_ORDER] = order_id ) RETURN ORDERS_STATUS[ID_ORDER] = order_id && ORDERS_STATUS[ID_STATUS] = maxStatus ) RETURN COUNTROWS ( t )% OF GT = VAR latestRows = [orders_amt] VAR allrows = CALCULATE ( SUMX ( VALUES ( STATUS_DICTIONARY[NAME] ), [orders_amt] ), ALL ( STATUS_DICTIONARY[NAME] ) ) RETURN latestRows / allrowsWorked like a charm! Thank you very much. You truly are a Rockstar 🙂
