Forum Discussion
summarize data
hi , i have created a measure MaxID and drag column Requisition_id to get a clustered column visualization. the data came as:
| MaxID | requisition_id |
| 1 | 99 |
| 4 | 100 |
| 5 | 101 |
| 4 | 102 |
| 5 | 103 |
| 4 | 104 |
| 5 | 105 |
| 4 | 106 |
| 5 | 107 |
| 5 | 108 |
| 4 | 109 |
| 5 | 110 |
| 5 | 111 |
| 4 | 112 |
| 5 | 113 |
| 5 | 114 |
| 5 | 115 |
| 5 | 116 |
| 5 | 117 |
actual data i need is given below:
| Max ID | Count of requisition_id |
| 1 | 35 |
| 2 | 32 |
| 3 | 14 |
| 4 | 129 |
| 5 | 229 |
| 6 | 17 |
| 7 | 117 |
please help.
Hi Anonymous
From you information, MaxID should be the max requisition_event_id per requisition_id, Count of requisition_id should be the count of requisition_id per MaxID, right?
in my test, [Measure] is the MaxID, i could also create a calculated column max to replace it, then create another column count for Count of requisition_id.
count = CALCULATE(COUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max])) max = CALCULATE(MAX([requisition_event_id,]),ALLEXCEPT(Sheet1,Sheet1[requisition_id,]))
Or if you need distintcount, you can use the following formula
distintcount = CALCULATE(DISTINCTCOUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))
Best Regards
Maggie
12 Replies
- Anonymous It will be great if you can share the sample data on which you have created the measure. Also, DAX formula that you used to create the measure.
hi PattemManohar thanks for reply:
Dax formula for Max ID is MaxID = MAx('eta requisition_life_cycles'[requisition_event_id])
and table is
id event_name, sequence required, requires_upload created_at updated_at 1 Created By User | Awaiting HOD's Approval 1 1 0 22-06-2018 7:24 22-06-2018 7:26 2 Approved by HOD | Awaiting Store's Approval 2 1 0 22-06-2018 7:25 22-06-2018 7:26 3 Approved by Store | Awaiting Delivery 3 1 0 22-06-2018 7:26 10-07-2018 6:48 4 Rejected by Store | Requisition Closed 3 0 0 22-06-2018 7:27 06-08-2018 15:02 5 Delivered | Requisition Closed 3 1 0 10-07-2018 6:49 09-08-2018 8:06 6 Rejected by HOD | Requisition Closed 2 0 0 06-08-2018 14:19 06-08-2018 14:19 7 Auto- approved 2 0 0 25-10-2018 16:37 25-10-2018 16:37 sorry the sample of table data is given below
id, requisition_id, requisition_event_id, upload_link, created_by, updated_by, created_at, updated_at 220 99 1 504 504 07-10-2018 20:06 07-10-2018 20:06 221 100 1 596 596 08-10-2018 11:35 08-10-2018 11:35 222 101 1 596 596 08-10-2018 11:40 08-10-2018 11:40 223 102 1 596 596 08-10-2018 11:47 08-10-2018 11:47 224 103 1 419 419 08-10-2018 12:25 08-10-2018 12:25 225 104 1 419 419 08-10-2018 12:27 08-10-2018 12:27 226 105 1 310 310 08-10-2018 14:15 08-10-2018 14:15 227 106 1 310 310 08-10-2018 14:15 08-10-2018 14:15 228 107 1 310 310 08-10-2018 14:16 08-10-2018 14:16 229 108 1 310 310 08-10-2018 14:16 08-10-2018 14:16 230 109 1 310 310 08-10-2018 14:17 08-10-2018 14:17 231 110 1 310 310 08-10-2018 14:17 08-10-2018 14:17 232 111 1 310 310 08-10-2018 14:18 08-10-2018 14:18 233 112 1 310 310 08-10-2018 14:19 08-10-2018 14:19 234 113 1 310 310 08-10-2018 14:20 08-10-2018 14:20 235 114 1 251 251 08-10-2018 15:00 08-10-2018 15:00
