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
Super UserHi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.