Working out % based on a changing total

[kathrynhmoss]

Hi All, 

I have a problem that i am struggling to resolve. Please bear with my whilst i explain this as it is quite complicated. 

I have a matrix which is showing the number of responses to a survey by ethnic group 

Table 1

Ethnicity	2020/21
Asian	984
Black	567
Chinese	503
Mixed/Other	687
White	4725
N/A	153
Total	7619

I then have a slicer on the page which has a number of different survey responses eg, I'm not ready, i am ready but need help, i already doing the thing that i wanted to do etc. 

When i apply the slicer to "I'm not ready" for example, the numbers look like this (below) In total there were 149 (out of 7619) people who said they were not ready. This makes up 2.0% of all possible responses. 

Table 2 - This is how the 149 figure is broken down 

Ethnicity	2020/21
Asian	8
Black	2
Chinese	15
Mixed/Other	9
White	113
N/A	2
Total	149

I want to be able to calculate the % of ethnic group that replied "i am not ready" as a % of the population for that specified ethnic group. So take Asian as an example, i need to be able to divide 8 by the 984 Asians who responded "i am not ready" = 0.8%. 

These figures below are the answers that i am trying to achieve. 

Ethnicity	2020/21
Asian	0.8%
Black	0.4%
Chinese	3.0%
Mixed/Other	1.3%
White	2.4%
N/A	1.3%
Total	Overall 2.0%

Anyone have any ideas about a measure/measures that i could write to get the correct percentages to display in my matrix please? 

Thanks in advance for any advice offered. 🙂

[Richard_100]

Hello

It's hard to solve this without seeing your data model and relationships, but approaching this as a % of subtotal question you may be able to use CALCULATE with ALL/REMOVEFILTERS

This is my quick mock up of a single table, and two sample pivot tables showing the subtotal of ethnicity ignoring status, and the division:

The DAX to achieve the % column is:

% Status of Ethnicity Subtotal:=

VAR Line_Sub = SUM([Count])
VAR Total_Sub = CALCULATE(SUM([Count]), ALL(Data[Status]))
RETURN DIVIDE (Line_Sub, Total_Sub)

Where [Count] is just the number of responses and [Status] is the column that contains "I am not ready" etc.

The ALL() is synonymous with REMOVEFILTERS() but I did this in Excel where REMOVEFILTERS is not available yet.  It removes the filter context from the [Status] column, allowing the SUM to get to the subtotal you need to divide by

Hope that helps

Regards

Richard

[kathrynhmoss]

Hi All, 

Thank you for your responses so far @Richard_100 and @Jihwan_Kim  I really do apprecite the time that you are taking to look into this. 

I feel that my inital post could have been clearer (sorry!) and i have some additional information to share with you that may further clarify what i am asking. 

I've included a picture of what things look like at the minute (i know it looks untidy at the moment ). Please see below. 

On the left is my slicer. These are the questions that were asked. I would like my colleagues to be able to select one of these at a time.  On the right is the matrix that i am using to display the responses based on the slicer selection. 

At the moment, the calculation in the matrix is doing (for example) the number of Asian students (8) out of a total of 456 (which is 149 + 307 - totals for each academic year). 

I need the calculation to be out of the total number of Asian students who responded to the survey which is 948. 

For 2020/21, the % of Asian students who said "i am not ready to start thinking about my career options yet" should show 0.8% (8/948) 

I think i need to specify somewhere in the model, the count of each ethnicity group, but i don't know if this can be done in one measure or whether it needs to be several. I also will then need to divide the count by the result of this new measure.  Any ideas please? 

Thanks, 

Kathryn

[Richard_100]

Hi Kathryn

Can you post a screenshot of your model/relationships as well please?

It remains I think a subtotal solution, you need a calculation to get to your appropriate denominator, i.e. the 948 for your Asian students.  So it will be some variation on using ALL or REMOVEFILTERS to take off the filter context that is limiting your subtotal to the underlying records allowed by your slicer on [Q1].  

VAR Line_Sub = SUM([Number])
// Will return the 8, within context of Q1 Asians

VAR Total_Sub = CALCULATE(SUM([Number]), ALL(Data[Q1]))
// Will return 948, by removing the Q1 context but it leaves the Asian context (and the academic year context) in place

RETURN DIVIDE (Line_Sub, Total_Sub)

The only tinkering left is the exact formulation of the above dependent on how your model is structured exactly.

Hope that helps

Regards

Richard

[kathrynhmoss · Accepted solution]

Hi All, 

Well, it appears that the issue is resolved, but not quite! 🙂 - i have implemented a workaround which is acceptable in my dashboard so i no longer need assistance with this issue, but thought i would post the solution here in case it helps someone else. 

The problem seems to be coming from the fact that i have got 2 years worth of data in my model which equals 15K+ rows in my spreadsheet. I am wanting to look at both years side by side in the matrix.

When i use the following DAX in a new spreadsheet with only 1 year worth of data (7619 rows of data), it works perfectly, but as soon as i try to use both years of data, this causes the % to split over the 2 years eg 50% 2020/21 and 50% 2021/22.

% of ethncity  =

DIVIDE(

COUNTA('Careers Check In Data'[Student ID]),

CALCULATE(COUNTAX('Careers Check In Data','Careers Check In Data'[Updated By Ethnicity]), ALLEXCEPT('Careers Check In Data','Careers Check In Data'[Updated By Ethnicity])),

0

)

If anyone has anything to add to this on how to make this work over the 2 years, please do. But the above DAX may help someone else if they are not trying to look over more than 1 year of data. 🙂

Thanks all to @Richard_100  and @Jihwan_Kim  for their suggestions. Very much appreciated. 🙂

Kathryn

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Expected result: =

IF (

SELECTEDVALUE ( Slicer[Slicer] ) = "I am not ready",

FORMAT (

DIVIDE ( SUM ( TableTwo[2020/21] ), SUM ( TableOne[2020/21] ) ),

"#,#0.00%"

),

SUM ( TableOne[2020/21] )

)