Skip to main content
Announcements
Join us on Feb.12 to Discover what’s possible with embedded analytics: REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
alexandrajaber
Contributor

100% Stacked bar chart issue (stack showing anything but 100% )

Hi All

 

I have been trying to create a 100% stacked bar chart with the below scenario:

We are trying to determine the % of each Main Reason for people leaving (6 possible response categories) / sum of all main reasons. We have used 2 group dimensions: 

1. Includes: age, band, gender...

2. Includes: Main reason and sub reason

 

Each leaver can select multiple reasons for leaving.

 

We have tried the following measure:

Measure 1= Sum(distinct {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} Leavers_Count)

Measure 2=count(distinct {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} EmployeeID)

Measure 3 =Sum(distinct {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} EmployeeID)

Measure 4= Sum( {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} Leavers_Count)
/Sum( Total <[Main Reason]> {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}>} Leavers_Count)

 

I have attached screenshots of the graph for each of the 4 measures (measure 1 and 2 show the same). Unfortunately, I am not able to attached the app or a sample app due to security reasons.

 

The results of the 4th measure makes the most sense but it adds up to 300%. 

 

Any suggestions or tips?

 

Thanks!

Labels (2)
3 Replies
Simon_Astakhov
Partner - Contributor III

Measure 4 is close enough. 😃

Your chart have 2 dimensions, but 100% u need by each.

Try Count( [reason] ) / Count( TOTAL <gender> [reason] )

It will return Count( [reason] ) for each reason type (six of them) and gender. And Total will return total values for each gender.

alexandrajaber
Contributor
Author

This kind of works whereby it gave me 100%, the issue now is that for each main reason we can have several sub reasons so we need to be able to count(distinct(EmployeeID)) somewhere because if 1 main reason has multiple sub-reasons, the % shown will not be accurate and the only way to deduplicate is if we count it based on EmployeeID.

How do you recommend we introduce that to the following expression: 

Count( {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}, [Exit Interview Eligible]={'True'}>} [Main Reason] ) /
Count( TOTAL <Gender, [Global Band (E)],Tenure, [Age Grouping], Country, [Company Code],Discover,Columbus,Nationality> {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}, [Exit Interview Eligible]={'True'}>}[Main Reason] )

 

?

 

Thanks!

alexandrajaber
Contributor
Author

I tried the following expression but the distinct part is bringing the stacked bar to where it doesn't show 100% anymore... when distinct is removed, the stack goes back to 100% and the numbers are pretty close to what they should be but not exact..

 

Count(DISTINCT {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}, [Exit Interview Eligible]={'True'}>} [EmployeeID] ) /
Count ( distinct TOTAL <Gender, [Global Band (E)],Tenure, [Age Grouping], Country, [Company Code],Discover,Columbus,Nationality> {<Period=, DateNum = {">=$(=$(v_CM_Start))<=$(=$(v_MaxDate))"}, DataType = {'Leavers'}, [Exit Interview Eligible]={'True'}>} [EmployeeID] )