Looking for DAX formula to calculate percentage per category for a year for a multiple choice ques

[LipiSingal]

I have done almost everything but not able to achieve the desired solution.

I have two surveys for 2020 and 2021 which have multiple-choice questions.

So there are two tables:

Table 1:

Survey ID respondent	Weightage	Survey year	Attractive region
505	1	2021	Western Europe
505	1	2021	Central & Eastern Europe
505	1	2021	Northern America
506	1	2021	Western Europe
506	1	2021	Other Asia
506	1	2021	Oceania
507	1	2021	Western Europe
507	1	2021	Central & Eastern Europe
507	1	2021	Northern America
508	1	2021	Central & Eastern Europe
508	1	2021	Japan
508	1	2021	Other Asia
1	0.7252292	2020	Central & Eastern Europe
1	0.7252292	2020	Western Europe
1	0.7252292	2020	Can't say
2	0.7470468	2020	Central & Eastern Europe
2	0.7470468	2020	Middle East
2	0.7470468	2020	Northern Africa
3	0.5661424	2020	Western Europe
3	0.5661424	2020	Northern America
3	0.5661424	2020	Middle East
4	1.4103518	2020	Other Asia
4	1.4103518	2020	China
4	1.4103518	2020	Latin America
5	1.1407595	2020	Northern America
5	1.1407595	2020	China
5	1.1407595	2020	Western Europe

Table 2:

Survey ID respondent	Weightage	Survey year
505	1	2021
506	1	2021
507	1	2021
508	1	2021
1	0.7252292	2020
2	0.7470468	2020
3	0.5661424	2020
4	1.4103518	2020
5	1.1407595	2020

Since it's a multiple-choice question, I want to calculate the percentage of respondents who chose e.g. western Europe as an attractive region based on total number of respondents for a survey. For this, I have linked the above two charts using Survey ID.

I want a measure to calculate the last column in below table 3:

	Weightages		Percentage I am getting from my measure	Percentage I want (required measure)
Attractive region			2020	2021	2020	2021
China	2.5511113		28%	0%	51%	0%
Western Europe	2.4321311	3	27%	33%	49%	75%
Northern America	1.7069019	2	19%	22%	34%	50%
Central & Eastern Europe	1.472276	3	16%	33%	29%	75%
Other Asia	1.4103518	2	16%	22%	28%	50%
Latin America	1.4103518		16%	0%	28%	0%
Middle East	1.3131892		15%	0%	26%	0%
Northern Africa	0.7470468		8%	0%	15%	0%
Can't say	0.7252292		8%	0%	15%	0%
Japan		1	0%	11%	0%	25%
Oceania		1	0%	11%	0%	25%
Total	13.7685891	12

For eg. for Western Europe, 3 respondents in 2021 survey have marked it as a attractive region. Now the percentage I want is 3 divided by 4 (respondents in 2021 survey). The percentage I am getting is 3/9 (respondents in 2020+2021 survey)

This is the DAX I am using:

Denominator = calculate(sum('Table 2'[Weightage]),allexcept('Table 2',Table 2[Survey year]))

%category = divide(sum('Table 1'[Weightage]),Denominator,0)

Please suggest the change in DAX function to require desired percentage. Since its a multiple choice question I am facing issues. I really need this to be resolved, please suggest what can be done. Thankyou.

[v-yangliu-msft · Accepted solution]

Hi  @LipiSingal ,

Here are the steps you can follow：

Regarding your first question:

Required percentage

1. Create measure.

Measure =
var _countgroup=
CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Survey year]=MAX('Table'[Survey year])&&'Table'[Attractive region]=MAX('Table'[Attractive region])))
var _countall=
CALCULATE(COUNTROWS('Table2'),FILTER(ALL('Table2'),'Table2'[Survey year]=MAX('Table'[Survey year])))
return
DIVIDE(_countgroup,_countall)

2. Result:

Regarding your second question:

Measure %= If( year= 2020, denominator to be sum of weightages from 2020, else denominator to be sum of weighatges from 2021)

1. Create measure.

Measure 2 =
var _select=SELECTEDVALUE('Table'[Survey year])
var _countgroup=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Survey year]=MAX('Table'[Survey year])&&'Table'[Attractive region]=MAX('Table'[Attractive region])))
var _selectweight=CALCULATE(SUM('Table'[Weightage]),FILTER(ALL('Table'),'Table'[Survey year]=_select))
return
DIVIDE(_countgroup,_selectweight)

2. Use [Survey year] as the slicer, when the slicer is 2020, the denominator is the sum of [Weight] in 2020, and when the slicer is 2021, the denominator is the sum of [Weight] in 2021

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LipiSingal]

@Greg_Deckler Thanks for your response. I tried your suggestion and it works fine for the 2021 survey. For a moment I thought its working fine but there was a slight difference in the numbers.

To explain:

In my original survey 2020 survey has 504 respondents, 2021 has 550 respondents. 

 This variable : ___Denominator seems to be calculating 550 as the denominator for both years. 

So in my graph, the percentage for 2020 is also coming as xxx/550, whereas it should be xxx/504. The percentage for 2021 is absolutely correct and thank you very much for this. 

Another issue: I have filters on my main dashboard where people can filter the survey results based on revenue, nationality etc. After using this measure, the filters don't seem to be working on this graph. Other graphs are changing based on the filters chosen, but not this one.

Looking forward to your reply. Thankyou.

[LipiSingal]

I am not aware with DAX a lot, I mostly google the various codes and try for my purpose. I had something like this in mind, but I only know how to do this in Excel, can anyone help me convert it to DAX for PBI and can something like this work?

Measure %= If( year= 2020, denominator to be sum of weightages from 2020, else denominator to be sum of weighatges from 2021)

So basically, if we can add a condition to calculate the denominator variably, this might help us solve this. 

In other words- this is just one of those scenarios where we have data for a multiple choice question from two years, and we want to divide the total of these by unique respondents from the respective year.

[Greg_Deckler]

@LipiSingal Perhaps try:

Measure 2 = 
    VAR __Year = MAX('Table 1'[Survey year])
    VAR __Denominator = COUNTROWS(SUMMARIZE(FILTER(ALL('Table 1'),[Survey year]=__Year),[Survey ID respondent]))
RETURN
    DIVIDE(SUM('Table 1'[Weightage]),__Denominator)