Is there a way to create a table consisting of multiple measures with aggregate values?

Question & Answer
,
1

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
image

Desired Output:
image

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

2

I would suggest using SQL to pivot your columns into rows.

Where then you would have
clinic | year | range | count
clinic A | 2019 | 0-50 | 40
Clinic A | 2019 | 51-100 | 50
etc…

Then it would be really simple as you would just add the range as a row in a table and sum the counts / take a percentage of total on the sum of the counts.