cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 NumberOpened Fiscal Year
12019
22020
32020
42020
52019
62019
72020

 

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

if your duplicate is on case number then do a distinctcount() rather than a countrows




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Highlighted
Super User I
Super User I

when you say you can't get past it, what are you expecting in comparison to what you are getting?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

@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. 

Super User I
Super User I

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

@vanessafvg 

 

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. 

 

AlyssaP17_0-1600970526376.png

 

Highlighted

if your duplicate is on case number then do a distinctcount() rather than a countrows




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors