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
Super User I
Super User I

Re: COUNT ROWS/FILTER & PERCENT DIFFERENCE

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

Re: COUNT ROWS/FILTER & PERCENT DIFFERENCE

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
Helper I
Helper I

Re: COUNT ROWS/FILTER & PERCENT DIFFERENCE

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

Highlighted
Super User I
Super User I

Re: COUNT ROWS/FILTER & PERCENT DIFFERENCE

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
Helper I
Helper I

Re: COUNT ROWS/FILTER & PERCENT DIFFERENCE

@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
Super User I
Super User I

Re: COUNT ROWS/FILTER & PERCENT DIFFERENCE

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!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors