% of total for clustered bar chart

jotg · March 14, 2023, 5:46am

Hi, after getting assistance with changing counts to percentage of total for a bar chart, I am now having trouble doing the same thing for a clustered bar chart.

This is what my visual looks like:

I then used this script to try and convert this to a %:

count({SwallowScreen_recode})/count(patientrecordid)

But putting this new calculated field in the value well, gave me this incorrect visual:

I couldn’t replace SwallowScreen_recode (in the group/colour well) with the new % calculated field, as it said “custom aggregation field is not allowed as a dimension”.

And if I remove swallowscreen_recode from the group/colour well, and just use %swallowscreen in the value well, my visual looks like this:

So I’m not sure what I’m doing wrong.

Thanks.

Max · March 14, 2023, 9:47pm

counts will count the row if it is not null.

Do you want to do distinct count?

distinct_count({SwallowScreen_recode})/distinct_count(patientrecordid)

jotg · March 14, 2023, 11:55pm

I do want to include the nulls. I recoded null to “Missing” using a calculated field.

Max · March 15, 2023, 2:07pm

Is distinct count what you are looking for? What does that give you?

Biswajit_1993 · March 15, 2023, 2:52pm

Hi @jotg you are asking Percent of Total for the particular Month right?

jotg · March 21, 2023, 5:28am

Hi Max, I changed the calculated field to

distinct_count({SwallowScreen_recode})/distinct_count(patientrecordid), then added this field to the values well.

My visual now looks like this which is incorrect as for Jan 2021 yes should be 53%, no 36%, unknown 3% and missing 8% :

.

jotg · March 21, 2023, 5:29am

Hi @Biswajit_1993 Yes, that’s correct.

Biswajit_1993 · March 21, 2023, 8:35am

Hi @jotg , can you try with this below formula.

on the Measure column put your Measure field and the date part put your Date field.

for me I am getting the desired result by using tis formula.

Thanks & Regards
Biswajit Dash

jotg · March 22, 2023, 1:52am

Hi @Biswajit_1993 , thank you. I used this script:

But there is some sort of error?

I’m also not sure why we’re using sum in the script rather than count?

Thank you for all of your assistance.

Biswajit_1993 · March 22, 2023, 4:10am

Hi @jotg can you put count in your case because in my case the field is different so I have take the sum but you can take as Count and check once.