How to work out percentage using both a measure and a column

[siobhanjessica]

Hi all.

I am trying to work out the total percentage of different populations that we have scanned on the program. 

I have created a measure to filter out the patients by location. For example, the Rotherham patients measure is:

Rotherham Patients =

CALCULATE ( 'TLHC Total Patients Measures'[Total Scans on Programme] ,

FILTER ('TLHC Total Patients Scanned', 'TLHC Total Patients Scanned'[Town] = "Rotherham" ) )

This calculates how many patients are from Rotherham from the total scanned table.

I can also change the measure to this one if it helps.

Mexborough Patients =

CALCULATE ( COUNTROWS ('TLHC Total Patients Scanned'), 'TLHC Total Patients Scanned'[Town] = "Mexborough" )

I have a column called population which states in each area that the patient is from, there is the population. This is a single amount per patient. So does not need anything doing to it. I have done a measure for each area, and now I need to work out the percentage of the population that is scanned. So I need a measure and a column to coincide in the new measure.

Is this possible? 

Thank you in advance

[siobhanjessica]

@johnt75 Hi, thank you for the speedy response. 

Amazing. Thank you so much! This has worked.

[johnt75 · Accepted solution]

I'll suggest a different approach, so that you don't need to create separate measures for each town.

Create a dimension table which lists all the towns and their populations. You can do this in Power Query, by duplicating or referencing your existing table, discarding all other columns and then removing duplicate values, or you could do it in DAX like

Population =
SUMMARIZE ( 'Table', 'Table'[Town], 'Table'[Population] )

Create a one-to-many relationship from this new table to your current table, and you can then use the Population table to filter the fact table. Put the town column from Population into visuals or slicers and it will break everything down by town.

You can then create a measure to show the % scanned as 

% scanned =
DIVIDE (
    COUNTROWS ( 'TLHC Total Patients Scanned' ),
    SUM ( 'Population'[Population] )
)

which should work at the grand total level as well as at the town level.