Solved: summarize data

Anonymous · ‎11-10-2018

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.

v-juanli-msft · ‎11-12-2018

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

View solution in original post

PattemManohar · ‎11-10-2018

@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.

Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion

Anonymous · ‎11-10-2018

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

Anonymous · ‎11-10-2018

hi @PattemManohar

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

Anonymous · ‎11-10-2018

@PattemManohar

or you can refer the final data. its more organized. u can see requisition id is repetitive and i am trying to get only max requisiion_event_id from given requisition_id

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
257	100	2		1	1	08-10-2018 20:21	08-10-2018 20:21
323	100	4		32	32	09-10-2018 11:21	09-10-2018 11:21
222	101	1		596	596	08-10-2018 11:40	08-10-2018 11:40
256	101	2		1	1	08-10-2018 20:21	08-10-2018 20:21
320	101	3		32	32	09-10-2018 11:20	09-10-2018 11:20
321	101	5		32	32	09-10-2018 11:20	09-10-2018 11:20
223	102	1		596	596	08-10-2018 11:47	08-10-2018 11:47
255	102	2		1	1	08-10-2018 20:21	08-10-2018 20:21
319	102	4		32	32	09-10-2018 11:20	09-10-2018 11:20
224	103	1		419	419	08-10-2018 12:25	08-10-2018 12:25
341	103	2		424	424	09-10-2018 16:29	09-10-2018 16:29
353	103	3		32	32	09-10-2018 17:20	09-10-2018 17:20
369	103	5		606	606	10-10-2018 10:20	10-10-2018 10:20
225	104	1		419	419	08-10-2018 12:27	08-10-2018 12:27
254	104	2		1	1	08-10-2018 20:20	08-10-2018 20:20
318	104	3		32	32	09-10-2018 11:19	09-10-2018 11:19

rocky09 · ‎11-10-2018

@AnonymousI am bit confused. Can you post some example how do you want to see the data. I know, you have mentioned in first post. But, it will be great if you post example with the last posted data.

Anonymous · ‎11-10-2018

@rocky09 HI

my main table is

d,	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
257	100	2		1	1	08-10-2018 20:21	08-10-2018 20:21
323	100	4		32	32	09-10-2018 11:21	09-10-2018 11:21
222	101	1		596	596	08-10-2018 11:40	08-10-2018 11:40
256	101	2		1	1	08-10-2018 20:21	08-10-2018 20:21
320	101	3		32	32	09-10-2018 11:20	09-10-2018 11:20
321	101	5		32	32	09-10-2018 11:20	09-10-2018 11:20
223	102	1		596	596	08-10-2018 11:47	08-10-2018 11:47
255	102	2		1	1	08-10-2018 20:21	08-10-2018 20:21
319	102	4		32	32	09-10-2018 11:20	09-10-2018 11:20
224	103	1		419	419	08-10-2018 12:25	08-10-2018 12:25
341	103	2		424	424	09-10-2018 16:29	09-10-2018 16:29
353	103	3		32	32	09-10-2018 17:20	09-10-2018 17:20
369	103	5		606	606	10-10-2018 10:20	10-10-2018 10:20
225	104	1		419	419	08-10-2018 12:27	08-10-2018 12:27
254	104	2		1	1	08-10-2018 20:20	08-10-2018 20:20
318	104	3		32	32	09-10-2018 11:19	09-10-2018 11:19

i ahve created measure from above table : Measure name is Max ID

MaxID = MAx('eta requisition_life_cycles'[requisition_event_id])

then i created a visualization with measure max id and drag requisition_id in clustered column chart i get data 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

but i need data as

Max ID	Count of requisition_id
1	35
2	32
3	14
4	129
5	229
6	17
7	117

v-juanli-msft · ‎11-12-2018

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

Anonymous · ‎11-13-2018

@v-juanli-msft Hi Maggie, thank you so much.......you made my day. As a new user, this is my first perfect visualization.

rocky09 · ‎11-10-2018

still, I don't get. Are you counting requisition_ids against requisition_event_id?

Anonymous · ‎11-10-2018

@rocky09 yes but against maximum reqisition_event id only.

rocky09 · ‎11-10-2018

I am not sure if i understand it correctly. Anyway, try to create New Table from Modeling tab and use this DAX. Make sure to replace the table name and column as per your data.

summarizedata = SUMMARIZE(Table1,Table1[requisition_event_id,],"Count",COUNT(Table1[requisition_id,]))

Anonymous · ‎11-10-2018

@rocky09 hi rocky thanks i get the data like

requisition_event id count

28 1896

but i need bifurcation according to requisition_event_id like you can see in table that requisition_id is repeated according to event id and i need count of requisition_id wrt count of max event id

summarize data

Helpful resources

Power BI DataViz World Championships - June 2026

Join our Fabric User Panel

Power BI Community Update - March 2026

How to Get Your Question Answered Quickly