Help with Measure

[Fusilier]

Help with measure

3 hours ago

Hi,

Probably best to illustrate what I am trying to achieve in PBI with the tables below in.

My data table consists of individual responses to a survey.

One of the questions is about safety in the neighbourhood and whether the respondent felt 'Fairly safe', 'Neither safe nor unsafe', 'Very safe' etc.

This table is a row count of the different responses by Age group: 

	Answer count:
Age group	Fairly safe	Neither safe nor unsafe	Very safe	Total Result
65	237	197	69	503
18-24	5	14	4	23
25-34	71	79	14	164
35-44	169	108	32	309
45-54	191	142	37	370
55-64	262	140	63	465
Total Result	935	680	219	1834

I've then created a measure to calculate the percentage in each Age group who say they feel 'Fairly safe', Very safe etc:

% result
Age group	Fairly safe	Neither safe nor unsafe	Very safe	Total Result
65	47.12%	39.17%	13.72%	100.00%
18-24	21.74%	60.87%	17.39%	100.00%
25-34	43.29%	48.17%	8.54%	100.00%
35-44	54.69%	34.95%	10.36%	100.00%
45-54	51.62%	38.38%	10.00%	100.00%
55-64	56.34%	30.11%	13.55%	100.00%
Total Result	50.98%	37.08%	11.94%	100.00%

This is the measure:

Pct =

DIVIDE(

    [Count],

    CALCULATE(

        [Count],

        ALLSELECTED('All Responses (2)'[Answer count],'All Responses (2)'[answer])))

So, I'm half way there.

 What I am struggling with is a measure to calculate the difference between the % for each Age group and the total % to produce this result:

Difference to Total
Age group	Fairly safe	Neither safe nor unsafe	Very safe
65	-3.86%	2.09%	1.78%
18-24	-29.24%	23.79%	5.45%
25-34	-7.69%	11.09%	-3.40%
35-44	3.71%	-2.13%	-1.59%
45-54	0.64%	1.30%	-1.94%
55-64	5.36%	-6.97%	1.61%

So for example

18-24, Fairly safe = 21.74%

Total, Fairly safe =  50.98%

Difference = -29.24%

I just can't get my head around how to write the measure. 

Pulling my hair out!

Can anybody help please?

Thanks in advance!

[DataInsights · Accepted solution]

@Fusilier,

To start, I unpivoted the data in Power Query to look like this:

Measures:

Count = SUM ( 'All Responses (2)'[Answer Count] )

Pct = 
DIVIDE (
    [Count],
    CALCULATE ( [Count], ALLSELECTED ( 'All Responses (2)'[Category] ) )
)

Pct to Total Variance = 
VAR vPct = [Pct]
VAR vNumerator =
    CALCULATE (
        [Count],
        ALLSELECTED ( 'All Responses (2)' ),
        VALUES ( 'All Responses (2)'[Category] )
    )
VAR vDenominator =
    CALCULATE ( [Count], ALLSELECTED () )
VAR vCategoryTotal =
    DIVIDE ( vNumerator, vDenominator )
VAR vResult = vPct - vCategoryTotal
RETURN
    vResult