cauculate the percentage by using numerator as top3 percentage and denominator except unsure and no

[Hayoung]

I would like to calculate the percentage by using numerator as top3 percentage and denominator as percenatge except unsure and no, and then multiply a indicator (1-TOP3[no %]-TOP3[unsure %]). Note that when calcualting the top 3, unsure and no cannot be included. Do anyone know how to modify below DAX?

Below is the sample data:( 

Name	% of workers	Answer text	Merged
A	0.102673797	Unsure	A10
A	0.038502674	No	A9
A	0.131550802	A8	A8
A	0.110160428	A7	A7
A	0.119786096	A6	A6
A	0.275935829	A5	A5
A	0.319786096	A4	A4
A	0.195721925	A3	A3
A	0.263101604	A2	A2
A	0.440641711	A1	A1
B	0.102673797	Unsure	A10
B	0.038502674	No	A9
B	0.131550802	A8	A8
B	0.110160428	A7	A7
B	0.119786096	A6	A6
B	0.275935829	A5	A5
B	0.319786096	A4	A4
B	0.195721925	A3	A3
B	0.263101604	A2	A2
B	0.440641711	A1	A1

My expected result is 100% stacked bar chart like if filter the Name by A:

48%

38%

10%

4%

Top 3 % = DIVIDE(SUMX(TOPN(3,TOP3,TOP3[% of workers],DESC), TOP3[% of workers]),SUM(TOP3[% of workers]))*(1-TOP3[no %]-TOP3[unsure %])

Other 3 % = DIVIDE(SUMX(TOPN(3,TOP3,TOP3[% of workers],DESC), TOP3[% of workers]),(SUM(TOP3[% of workers]) - CALCULATE(SUM(TOP3[% of workers]), TOP3[Merged(Q&A)] in {"A9: No","A10: Unsure"})))*(1-CALCULATE(SUM(TOP3[% of workers]), TOP3[Merged(Q&A)] in {"A9: No","A10: Unsure"}))

no % = DIVIDE(CALCULATE(SUM(TOP3[% of workers]),TOP3[Answer text]="No"),SUM(TOP3[% of workers]))

unsure % = DIVIDE(CALCULATE(SUM(TOP3[% of workers]),TOP3[Answer text]="Unsure"),SUM(TOP3[% of workers]))

[MFelix]

Hi @Hayoung ,

Believe your issue is on the calculations of the No and Unsure try the following measures:

no % = CALCULATE(SUM(TOP3[% of workers]),TOP3[Answer text]="No")

unsure % = CALCULATE(SUM(TOP3[% of workers]),TOP3[Answer text]="Unsure")

Top 3 % = DIVIDE(
    SUMX(TOPN(3,TOP3,TOP3[% of workers],DESC), TOP3[% of workers]),
    SUM(TOP3[% of workers])-[no %] - [unsure %])
    *(1-TOP3[no %]-TOP3[unsure %])

Other 3 % = 1 - TOP3[no %] - TOP3[unsure %] - TOP3[Top 3 %]

Since your values are already percentages, making the division will break you values even further.

Result:

[Hayoung]

Thanks! got it! I would like to ask if you know what is the problem in below case. I would like to create a measure for sum of A14 and A15 after some adjustment, i.e. 0.53/sum(A1:A16)+0.11/(A1:A16). i have tried 

A14+A15 % = DIVIDE(CALCULATE(SUM(TOP5[% of workers]),TOP5[Merged]="A14"||TOP5[Merged]="A15"),SUM(TOP5[% of workers]))

but it seems not correct. It should return 29%, but it return 34%

% of workers	Merged
0.254545455	A16
0.530481283	A15
0.109090909	A14
0.054545455	A13
0.08342246	A12
0.048128342	A11
0.021390374	A10
0.097326203	A9
0.057754011	A8
0.021390374	A7
0.176470588	A6
0.080213904	A5
0.037433155	A4
0.198930481	A3
0.081283422	A2
0.376470588	A1

[MFelix]

Hi @Hayoung ,

The calculation seems correct based on the dataset you have provided

If you make some filters to the Merged column then I can get different results but for all the selection is ok. Do you have any filters based on the Merged column if yes you can wrap you last sum in a ALL statement that will keep the values for the calculation the same:

A14+A15 % = DIVIDE(CALCULATE(SUM(TOP5[% of workers]),TOP5[Merged]="A14"||TOP5[Merged]="A15"),CALCULATE(SUM(TOP5[% of workers]), ALL(TOP5[Merged])))

[Hayoung]

Yes, it now return 29%. Probably is because of my dataset actually include both last and current result. And thus, bring a new question. I have tried below for the top 5 % [ sum of top5/(a1:a16)]and i should only take current result and I expect return 50%. However, it give 69% for my result

Q25_Top 5 % = DIVIDE(CALCULATE(SUM(Q25_TOP5[% of workers]),TOPN(5,Q25_TOP5,CALCULATE(SUM(Q25_TOP5[% of workers]),DESC)), Q25_TOP5[% of workers]),CALCULATE(SUM(Q25_TOP5[% of workers]),ALL(Q25_TOP5[Merged])))

[MFelix]

Hi @Hayoung ,

If you allow me I believe there is a miss calculation of some sort on your formulas.  When picking up the no % for name A we get the following values:

No % = 0,03850262674 / 1,997860962 =  0.19271925 = 1.93%

How are you getting the 10% is this not the sum of the no (0.038502674 - A9) divided by the sum of all the A values?

Believe that the same tough you apply for this will be applied to the unsure, then  we can check the other formulas.

[Hayoung]

I would like it to be 100% stacked bar chart.  it should be 10% for unsure while 4% for no.