Distinct Orderid group by profile

[Anonymous]

If you take Idorder FGY for example, I have two different id's c475 and 481e with same element.What I want is distintc ids and group by pro in the expected out below:

Sample Data

ids	Price	total	Pro	Element	Idorder
c475	699	1398	Ad	xible	FGY
c475	699	1398	Ad	xible	FGY
481e	699	1398	Ad	xible	FGY
481e	699	1398	Ad	xible	FGY
6346	369	2775	Ad	exible	DMF
6346	369	2775	Ad	Nonexible	DMF
ecdo	369	2775	Ad	exible	DMF
ecdo	369	2775	Ad	Nonexible	DMF
4f10	556	2775	Ad	exible	DMF
4f10	556	2775	Ad	Nonexible	DMF
efef	556	2775	Ad	exible	DMF
efef	556	2775	Ad	Nonexible	DMF
7f36	556	2775	Ad	exible	DMF
7f36	556	2775	Ad	Nonexible	DMF
o4dy	369	2775	Ad	exible	DMF
o4dy	369	2775	Ad	Nonexible	DMF
a12b	0	219	dent	Nonexible	THN
a12b	0	219	dent	Nonexible	THN
a12b	219	219	dent	Nonexible	THN
a12b	219	219	dent	Nonexible	THN
CB65	138	414	Ad	exible	77LL
CB65	138	414	Ad	exible	77LL
C7DC	69	414	chi	exible	77LL
C7DC	69	414	chi	exible	77LL
220F	69	414	chi	exible	77LL
220F	69	414	chi	exible	77LL
D88B	138	414	Ad	exible	77LL
D88B	138	414	Ad	exible	77LL
2032	0	205	Ad	exible	9MA
2032	0	205	Ad	exible	9MA
2032	205	205	Ad	exible	9MA
2032	205	205	Ad	exible	9MA

Expected Sample Table Report in PowerBI:

Pro	elemt	id	Total
AD	exible	77LL	276
Chi	exible	77LL	138
Ad	exible	9MA	205
AD	exible	DMF	1,688.00
AD	Nonexible	DMF	1,107.00
Ad	xible	FGY	699
Ad	xible	FGY	699
dent	Nonexible	THN	219

[v-yuta-msft]

@Anonymous ,

If you take Idorder FGY for example, I have two different id's c475 and 481e with same element.What I want is distintc ids and group by pro in the expected out below:

---

Could you please clarify more details about the logic of grouping by? In addtion, are 'Ad' and 'AD' same in the Pro column?

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft  ad and AD are in the same pro column was just  a typo with caps

[Anonymous]

Measure =

VAR SumTrip = SUMMARIZE('Table (2)','Table (2)'[Elemt],'Table (2)'[ids])

RETURN

SUMX(SumTrip,MAX('Table (2)'[Price]))

The measure above almost gave me what I want but the highlited ID and element measure total is wrong and what I expect is :

AD	exible	DMF	1,688.00
AD	Nonexible	DMF	1,107.00

I need help fixing the measure and I have attached a link to the PowerBI file

Pbix file attached 

@v-yuta-msft @Mariusz 

[Mariusz]

Hi @Anonymous 

If you place all these columns in a table visual it will apply group by as default, have you tried that?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@Mariusz if i drag the columns in a Table visual, It is not giving me what I want 100%, please see below the result when I put all these columns in a table visual:

 

If you take ID: "DMF for example has 12 rows, 2 disticnt elemt (exible and non exible),6 unique ids but belong same pro categories, if you remove the duplicated ids, the result should be as below for DMF, 

AD	exible	DMF	1,688.00
AD	Nonexible	DMF	1,107.00

The Expected result is below :

Pro	elemt	id	Total
AD	exible	77LL	276
Chi	exible	77LL	138
Ad	exible	9MA	205
AD	exible	DMF	1,688.00
AD	Nonexible	DMF	1,107.00
Ad	xible	FGY	699
Ad	xible	FGY	699
dent	Nonexible	THN	219

Please see attached powerBI file with sample data 

powerBI file attached 

I hope this explanation helps

[v-yuta-msft]

@Anonymous ,

Could you please clarify the step of “remove the duplicated ids” and show the logic of achieving 1,688.00 and 1,107.00?

Regards,

Jimmy Tao