cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Working out % based on a changing total

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?

1 ACCEPTED SOLUTION
Helper I

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
5 REPLIES 5
Resolver I

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

Helper I

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

Resolver I

Hi Kathryn

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

Helper I

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
Super User

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] )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.