Help with Measure

Question

Help with measure3 hours agoHi,Probably best to illustrate what I am trying to achieve in PBI with the tables below in.&nbsp;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:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Answer count:&nbsp;&nbsp;&nbsp;Age groupFairly safeNeither safe nor unsafeVery safeTotal Result652371976950318-2451442325-3471791416435-441691083230945-541911423737055-6426214063465Total Result9356802191834&nbsp;&nbsp;&nbsp;I've then created a measure to calculate the percentage in each Age group who say they feel 'Fairly safe', Very safe etc:&nbsp;% result&nbsp;&nbsp;&nbsp;&nbsp;Age groupFairly safeNeither safe nor unsafeVery safeTotal Result6547.12%39.17%13.72%100.00%18-2421.74%60.87%17.39%100.00%25-3443.29%48.17%8.54%100.00%35-4454.69%34.95%10.36%100.00%45-5451.62%38.38%10.00%100.00%55-6456.34%30.11%13.55%100.00%Total Result50.98%37.08%11.94%100.00%&nbsp;This is the measure:Pct =DIVIDE(&nbsp; &nbsp; [Count],&nbsp; &nbsp;&nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [Count],&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;ALLSELECTED('All Responses (2)'[Answer count],'All Responses (2)'[answer])))&nbsp;So, I'm half way there.&nbsp;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:&nbsp;Difference to Total&nbsp;&nbsp;&nbsp;Age groupFairly safeNeither safe nor unsafeVery safe65-3.86%2.09%1.78%18-24-29.24%23.79%5.45%25-34-7.69%11.09%-3.40%35-443.71%-2.13%-1.59%45-540.64%1.30%-1.94%55-645.36%-6.97%1.61%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;So for example18-24, Fairly safe = 21.74%Total, Fairly safe =&nbsp; 50.98%Difference = -29.24%&nbsp;I just can't get my head around how to write the measure.&nbsp;Pulling my hair out!Can anybody help please?Thanks in advance!

datainsights · Accepted Answer

Fusilier,
&nbsp;
To start, I unpivoted the data in Power Query to look like this:
&nbsp;

&nbsp;
Measures:
&nbsp;
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
&nbsp;

&nbsp;

Forum Discussion

Help with Measure

1 Reply

Related content

Measure

DAX Measure

Path Measure

% of total measure

Count Measure


	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

% 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%

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%