So I have a dataset that consists clinic A, clinic B, clinic C etc, for various years (i.e. 2019,2020,2021,etc). There are measures that consists of aggregated counts for various ranges (0-50, 51-100,101-150, 150-beyond) for each clinic and year.

So let’s say Clinic A has values of 40 between 0-50,50 between 51-100, 30 between 101-150 and 20 with 150 or more.

I need to create a table that both displays the values between the various ranges, which is simply just including the measure associated with each range. However, I then need to create a column consisting of the percentage of each range where the denominator are the sum of the ranges. So 0-50 for example is 40/140,51-100 is 50/140,etc.

Is there a technique anyone might recommend, outside of having to create a calculated field for each range?

If any further explanation is needed, would be happy to provide. Additionally, once the percentages are calculated, will also need to create a pie chart with the percentage of ranges as well.

In Summary:

Dataset

Desired Output:

Where the % is using the Sum of 0-beyond as the denominator (Sum of 4 aggregate measures essentially)