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

[alexandrajaber]

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!

[Simon_Astakhov]

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]

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]

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] )