Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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).
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
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).
How is 1/4 = 20%? is it 1/5 or are you looking to get 25%?
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).
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))
Hi Sunny,
You are genius and so much helpful.
Thanks for the help