topic 100% Stacked bar chart issue (stack showing anything but 100% ) in App Development

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

alexandrajaber — Sat, 16 Nov 2024 20:31:51 GMT

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!

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

Simon_Astakhov — Wed, 03 Jul 2019 15:04:12 GMT

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.

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

alexandrajaber — Thu, 04 Jul 2019 15:03:21 GMT

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!

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

alexandrajaber — Fri, 05 Jul 2019 11:41:42 GMT

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