Forum Discussion

kathrynhmoss's avatar
kathrynhmoss
Icon for Helper I rankHelper I
4 years ago
Solved

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 su...
DAX
Matrix Visual
Measures
percentage
  • kathrynhmoss's avatar
    kathrynhmoss
    4 years ago

    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
kathrynhmoss's avatar
kathrynhmoss
Icon for Helper I rankHelper I
4 years ago

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's avatar
Richard_100
Icon for Resolver I rankResolver I
4 years ago

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's avatar
    kathrynhmoss
    Icon for Helper I rankHelper I
    4 years ago

    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