Forum Discussion
Helper IWorking out % based on a changing total
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
Resolver IHi 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
Helper IHi 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.