Distinct in the pivot chart

[mhamurcuoglu]

Dear all,

I want to switch the table below to pivot table as an example like the second table. But, I need to seperate belonging to groups and if the meterial IDs are the same number, it have to use the bigger operation number's material ID to put down under the group name.

Is there anyone that can help about this?

t

District	Group	Material Id	Operation
1	R2	1001	5
1	R3	1001	4
2	R2	1005	4
2	R4	1008	3
3	R2	1111	3
4	R3	1120	1

P

District	Group	R1	R2	R3	R4
1			1(count Material Id
2			1		1
3			1
4				1

[santhoo_san]

did not understand the requirement

[Anonymous]

Hi

like this!!

Regards!!

Jannet

[mhamurcuoglu]

Hi Santosh & Janet;

I agree that I couldn't explain the situation..    

Let me try again.

I just want to count the biggest number operation if district ID and material ID's are same.

For example: lets say the district Id=1 and Material ID is 1001 for 2 records. But the operation ID's are 5 and 4

In this situation, I want to count only operation Id 5(highest number) only. Which the report should seems like;

Group    Count

--------     -------

R2             1

I hope that I can explain the situation..

Many thanks in advance...

[Anonymous · Accepted solution]

Hi Mehmet

I solved this issue.

[santhoo_san]

To add to palladium‌you can use below expression for distinct count

max(aggr(count(DISTINCT [Material Id]),District,[Material Id]))

[mhamurcuoglu]

Thanks        Алекс Зарин

it is work