% of total for clustered bar chart

[jotg]

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]

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]

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

[Max]

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

[Biswajit_1993]

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

[jotg]

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]

Hi @Biswajit_1993 Yes, that’s correct.

[Biswajit_1993]

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]

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]

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.

[jotg]

Hi @Biswajit_1993,

I’m still getting an error:

I wonder if it’s because swallowscreen_record is a text field and date of admission is a date field?

[Biswajit_1993]

Can you put one square bracket on the Date field.

[jotg]

Also an error:

[Max]

It looks like you haven’t enclosed your percentOfTotal