I have a card where we are looking at blood draw procedures year to date, graphed by month. So, we have the date of the blood draw as a column, we have the count of distinct needle sticks, and then one column that categorizes each blood draw as successful or unsuccessful.
We are not so much worried about the actual counts, rather the success percentage rate per week.
The 100% stacked bar chart was useful in the sense that it turned the Y axis into percentages instead of count of blood draws. It will assign the percentage of successful and the percentage of unsuccessful. But the problem is that every week is going to add up to 100%, and so I don't know how to sort the bars by percent of successful. In other words, if week 1 had 10 blood draws and a 90% success rate, and week 2 had 20 blood draws but a 50% success rate, I want it to sort by the higher success rate, not the higher blood draw count.
My next thought was to do a beastmode. If the "success/unsuccess" colmumn is "success" then '1' else '0'. Then I would sum that column and divide it by the total count of draws. Then my success column would be a percentage on its own, versus converting counts into percentages in the graph. The problem I have here is that the count of draws is a distinct count because there are duplicates. So when I sum the new 1 or 0 column, it sums the duplicates. So in a distinct count of the draws, there may be a distinct count of 4 draws that were successful. But in my '1' or '0' column, if there is a dupplicate successful draw, they are each given a 1 and they are each summed in my sum of successful draw beastmode. So I could have 4 draws with a sum of 5 successful, which isnt correct.
I think I could solve this with a rank and window in ETL, but we're trying to find a way to resolve this without creating another flow.
