Forum Discussion
COUNT ROWS/FILTER & PERCENT DIFFERENCE
I am working on a data set where I need to find the percentage change from 2019 to 2020. My data is structured as follows:
Table
| Case Number | Opened Fiscal Year |
| 1 | 2019 |
| 2 | 2020 |
| 3 | 2020 |
| 4 | 2020 |
| 5 | 2019 |
| 6 | 2019 |
| 7 | 2020 |
I have tried the following and cannot get past the count/filter expressions. Any tips are apprecatied.
FY20Cases = CALCULATE(
COUNTROWS(Table),
FILTER(Table,Table[Opened Fiscal Year="2020"),
FY19Cases = CALCULATE(
COUNTROWS(Table),
FILTER(Table,Table[Opened Fiscal Year="2019"),
DIFF=[FY20 CASES]-[FY19CASES]
DIFF%= (DIFF/[FY19CASES])*100
- if your duplicate is on case number then do a distinctcount() rather than a countrows
5 Replies
- when you say you can't get past it, what are you expecting in comparison to what you are getting?
vanessafvg I receive errors on the formulas. I am not getting anything in return. I have tried to rework the formulas and tried new expressions but I only receive errors.
Ive rerun your code and it works for me, see attached.
check your data types that might be an issue
also what error messages are you getting can you get a screenshot.
see the attached power bi file
UPDATE! I have figured out the issue - I have duplicate rows for a reason that is why the numbers are higher. Any idea on how to work around that?
I re-ran the code and it works. But the outputs are not correct. By using a pivot table I can see what the total cases were for FY19 and FY20. I will check my data types. See image below.
- if your duplicate is on case number then do a distinctcount() rather than a countrows