
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Percentage display on a Stacked Bar Chart
Hello All,
Here is my issue:
I am trying to create a stacked bar chart that represents number of employees by Age group for different departments.
So I managed to do that with the following formula for meassure:
count({<[Employee Current Status Short Descrip] = {'Active','On Leave'}>} distinct EMPLOYEE)
And Dimension 1 is Department and Dimension 2 is Class(Current Age,10).
Now it works and it shows number of employees for each Age Group by department.
But what I have a request for is to represent relative % values for each Age Group by department.
So I tried something like this
count({<[Employee Current Status Short Descrip] = {'Active','On Leave'}>} distinct EMPLOYEE)
/
count({<[Employee Current Status Short Descrip] = {'Active','On Leave'}>} total <Department> distinct EMPLOYEE)
but it does not work because one employee can be in different Age group as my data is for multiple years and by using distinct function my total is less that it suppose to be.
For Example for department 1 I have 3 employee in 20<=yr<30; 5 employee in 30<=yr<40, 10 employee 40<=yr<50. So obviously my total should be 18 bu because I have two employees that moved from one group to another during the period my total is 16 and it gives wrong results.
Does anyone has any idea how I can solve this?
Thanks
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try it this way....
1) Modify your script to create Current_Age_Bucket in the script
Table: LOAD UniqueIdentif, EMPLOYEE, Date, [Employee Current Status], FiscalYear, [Employee Current Status Short Descrip], [Employee Current Status Descrip], [Current Age], [Foundation Department], Class([Current Age],10) as Current_Age_Bucket FROM [..\..\Downloads\pull for bar chart percent.xlsx] (ooxml, embedded labels, table is Sheet1);
2) Use this expression
Count({<[Employee Current Status Short Descrip] = {'Active','On Leave'}>} DISTINCT EMPLOYEE)
/
Sum(TOTAL <[Foundation Department]> Aggr(Count({<[Employee Current Status Short Descrip] = {'Active','On Leave'}>} DISTINCT EMPLOYEE), [Foundation Department], Current_Age_Bucket))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you check if this works for you
Count({<[Employee Current Status Short Descrip] = {'Active', 'On Leave'}>} DISTINCT EMPLOYEE) / Aggr(NODISTINCT Count({<[Employee Current Status Short Descrip] = {'Active', 'On Leave'}>} DISTINCT EMPLOYEE), Department)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unfortunately it still produced the distinct count.
Thanks for a quick replay.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please see the attached file with the data I am working with.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try it this way....
1) Modify your script to create Current_Age_Bucket in the script
Table: LOAD UniqueIdentif, EMPLOYEE, Date, [Employee Current Status], FiscalYear, [Employee Current Status Short Descrip], [Employee Current Status Descrip], [Current Age], [Foundation Department], Class([Current Age],10) as Current_Age_Bucket FROM [..\..\Downloads\pull for bar chart percent.xlsx] (ooxml, embedded labels, table is Sheet1);
2) Use this expression
Count({<[Employee Current Status Short Descrip] = {'Active','On Leave'}>} DISTINCT EMPLOYEE)
/
Sum(TOTAL <[Foundation Department]> Aggr(Count({<[Employee Current Status Short Descrip] = {'Active','On Leave'}>} DISTINCT EMPLOYEE), [Foundation Department], Current_Age_Bucket))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, That does the work, thank you.
The only thing I wanted to keep the Age Group as a calculated dimension so user can change the age range if they want, but I guess the only way to do it is with aggr function and it cannot be used with calculated dimension.
Am I right?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unfortunately, what you have said is all correct. 😞

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sound good.
Thank you for the solution.
Hope they would not insist to have the age group flexible.....
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- « Previous Replies
-
- 1
- 2
- Next Replies »