Subtracting Matrix Table Columns and Creating a new Matrix Table

[fereshtehaghaei]

In Microsoft Excel, I have 2 pivot tables one is Active Counts for each term and the other one is Attrition Count. The way it is being done in Excel Microsoft is as show in image C4-B4 and we get the attrition number. My company has moved away from using Excel and I need to recreate these in Power BI RS Desktop. I have a Matrix table that does have my Active Student count in that Matrix table and I do have a measure that calculates my Active count based on my logics. Term Label and it's in this order for example: Spring 2010 Summer 2010 and Fall 2010 Spring 2011 Summer 2011 Fall 2011 my terms are by Spring Summer Fall and year. So I can't have all Springs listed and then all summers. Therefore, all my Terms (which are strings not dates) are being sorted out by a number called Term Ordinal. Start Terms or, I should say our Terms are in this order: Spring Summer Fall for example it goes like this Spring 2021, Summer 2021, Fall 2021, Spring 2022, Summer 2022, Fall 2022, Spring 2023, Summer 2023, Fall 2023 and so on. I have created separate tables (Term Ordinal) in order to sort my terms and prevent circular dependency errors. All pieces are almost done except the Attrition Count.

Also there are terms that there is no active count, I still need to incorporate them in my subtraction or count them in my formula as zeros.

Let's say my Active count Matrix table the column is Summer 2021 (this will be considered Previous term) then the next column which is Fall 2021 (is considered Current term). Therefore, I need to Subtract Fall 2021 (active counts) - Summer 2021 (active counts) + Fall 2021 (graduated counts).

For instance for no values in Active count let's say Spring 2023 column and Summer 2021 row has no values but Fall 2022 column and Summer 2021 row has a value I still want that no value as a zero minus the value in previous column so 0-1.

The Screenshot I provided from Excel shows if we had 11 students in SU22 and 2 students in F22 and 8 Graduates in SU22 then 2-11+8 = -1 and these results need to be recorded under SU22 (which is the previous term). In my attrition table, I would like to have the column total and row total as well as the Attrition Rate. As shown in Pivot tables in Excel.

I really hope this is possible in the Power BI RS desktop. My Power BI RS Desktop Doc is attached as a link. Thank you in advance for looking at my question.   https://drive.google.com/file/d/19jV1dTXdmng_pd_kujr6m-OJ8zv_rD2H/view?usp=sharing                              

[Anonymous]

Hi @fereshtehaghaei ,

Can't open the link you provided at the moment, is it possible to reshare it?

Best Regards,
Adamk Kong

[fereshtehaghaei]

Hi @Anonymous Please let me know if this link would open and works: https://drive.google.com/file/d/19jV1dTXdmng_pd_kujr6m-OJ8zv_rD2H/view?usp=sharing

[_AAndrade · Accepted solution]

Hi @fereshtehaghaei,

Please see if this could help you.

[fereshtehaghaei]

Hi @_AAndrade I place the Term Ordinal as my columns and I was able to slice the data and see it by term. However, another request that is being very tricky is to place the result under previous term. In another words: If Fall 2021 has 6 actives and Summer 2021 has 8 actives the result of -2 should be placed under Summer 2021 I tried to change the attrition formula but not having any luck to get this last piece working. Would you please help or guide me? I certainly appreciate your help and time and your solution has been the best so far and I am so grateful you took the time and looked into this solution. Looking forward to hearing from you.