Bar chart creation using particular fields from dimensions

[sagarjagga]

Hi All,

I am facing a challenge to create a bar chart using 2 dimensions for below data set.

Manager	Supervisior	PAT Roll No
Anindya	ABC	1
Anindya	ABC	11
Anindya	ABC	22
Anindya	ABC	33
Anindya	DEF	10
Anindya	DEF	10
Mehtab	ABC	33
Mehtab	FED	44
Sagar	ABC	21
Sagar	FED	33

I need to create bar chart only for Anindya and sagar who are under manager and for supervisiors ABC,FED and bar chart should be 'grouped bar chart' and it should show share of count of 'PAT Roll No' under supervisior and manager dimension in respective bars. for example count of PAT roll no in Anindya in ABC and FED is 4(as there is no FED for anindya) so ABC bar chart will show 100%(4/4) and for FED it will be 0%

Then for sagar count of pat roll no under ABC and FED is 2(1+1) so 50% for ABC bar(1/2) and 50% for FED bar(1/2)

Thanks,

[sagarjagga]

Manager	Supervisior	PAT Roll No
Anindya	ABC	1
Anindya	ABC	11
Anindya	ABC	22
Anindya	ABC	1
Anindya	ABC	22
Anindya	ABC	33
Anindya	FED	1
Anindya	DEF	10
Anindya	DEF	10
Mehtab	ABC	33
Mehtab	FED	44
Sagar	ABC	22
Sagar	FED	33

Manager	Supervisior	Count Distinct PAT Roll No	Percentage of Share
Anindya	ABC	4	4/5=80%
Anindya	DEF	1	1/4=20%
Sagar	ABC	1	1/2=50%
Sagar	FED	1	1/2=50%

I am finding out the share of ABC,DEF in ABC and DEF for these two managers. but by using previous denominator expression Total for Anindya -> ABC,DEF is coming 4(because it is counting PAT roll no1 as single unit but it should have calculate PAT roll no 1 from FED as well).

[sagarjagga]

Manager	Supervisior	Count Distinct PAT Roll No	Percentage of Share
Anindya	ABC	4	4/5=80%
Anindya	DEF	1	1/5=20%
Sagar	ABC	1	1/2=50%
Sagar	FED	1	1/2=50%

Final chart or table

[sagarjagga]

Sorry in previous replies i baffled in FED and DEF

Manager	Supervisior	PAT Roll No
Anindya	ABC	1
Anindya	ABC	11
Anindya	ABC	22
Anindya	ABC	1
Anindya	ABC	22
Anindya	ABC	33
Anindya	FED	1
Anindya	DEF	10
Anindya	DEF	10
Mehtab	ABC	33
Mehtab	FED	44
Sagar	ABC	22
Sagar	FED	33

Manager	Supervisior	Count Distinct PAT Roll No	Percentage of Share
Anindya	ABC	4	4/5=80%
Anindya	FED	1	1/4=20%
Sagar	ABC	1	1/2=50%
Sagar	FED	1	1/2=50%

I am finding out the share of ABC,DEF in ABC + FEDfor these two managers. but by using previous denominator expression Total for Anindya -> ABC,DEF is coming 4(because it is counting PAT roll no1 as single unit but it should have calculate PAT roll no 1 from FED as well).

[sunny_talwar]

How is 1/4 = 20%? is it 1/5 or are you looking to get 25%?

[sagarjagga]

1/5 = 20%

Manager	Supervisior	Count Distinct PAT Roll No	Percentage of Share
Anindya	ABC	4	4/5=80%
Anindya	FED	1	1/5=20%
Sagar	ABC	1	1/2=50%
Sagar	FED	1	1/2=50%

I am finding out the share of ABC,FED in ABC + FED for these two managers. but by using previous denominator expression Total for Anindya -> ABC,FED is coming 4(because it is counting PAT roll no1 as single unit but it should have calculate PAT roll no 1 from FED as well).

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])/

Sum(TOTAL <Manager> Aggr(Count(DISTINCT {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No]), Manager, Supervisior))

[sagarjagga]

Hi Sunny,

You are genius and so much helpful.

Thanks for the help