Identify items with positive growth year-over-year

[JaysYee]

I've created a measure based on this post to calculate year-over-year growth.  It works great.  However, now I want to filter the items that have consistent (or positive growth) for every year.  How do I go about creating that slicer/filter?

For example:

Item	2021	2022	2023
A		-5%	15%
B		10%	5%
C		20%	-3%

Use a slicer with a dropdown that will filter the items with only positive growth for all years.  So if I select some dropdown choice called "Positive Growth," I will only see item B.

Thanks!

[Anonymous]

Hi @JaysYee 

First, you should create two calculate columns:

Positive Growth = CALCULATE(SELECTEDVALUE('Table'[Item]),FILTER(ALL('Table'),'Table'[2021]>=0 && 'Table'[2022]>=0 && 'Table'[2023]>=0 && 'Table'[Item] = EARLIER('Table'[Item])))

Negative  Growth = CALCULATE(SELECTEDVALUE('Table'[Item]),FILTER(ALL('Table'),'Table'[2021]<=0 && 'Table'[2022]<=0 && 'Table'[2023]<=0 && 'Table'[Item] = EARLIER('Table'[Item])))

Then, create a field  parameter:

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JaysYee]

Thank you!  I should have noted that the table you see is a result of a dynamic measure calculating year-over-year growth.  How can I modify your calculated column to accommodate this measure?  I've copied and pasted the measure I used from a different post:

VAR _currentyearpassenger = [Passengers total:]
VAR _currentyear = MAX ( 'Year'[Year] )
VAR _previousyearpassenger = CALCULATE ( [Passengers total:], 'Year'[Year] = _currentyear - 1 )
VAR _YoYGrowth = DIVIDE (_currentyearpassenger - _previousyearpassenger, _previousyearpassenger)

RETURN
IF (
HASONEVALUE ( 'Year'[Year] ),
IF (
NOT ISBLANK ( _currentyearpassenger ) && NOT ISBLANK ( _previousyearpassenger ),
_YoYGrowth
)
)

Finally, the end table should completely remove the item row if it doesn't fit the filter, not simply hide the name.

Thank you again!

[Anonymous]

Hi  @JaysYee 

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JaysYee]

@Anonymous , I've attached a sample file with dummy data.  I would like to add a field to the slicer where it would filter for rows where the client has positive YoY growth for all years (e.g., Client C in sample file).  Whether the sub-row (e.g., Sales Person in sample file) has positive growth is not relevant.  In other words, I'm only concerned with client level aggregation.  Thank you so much!

I had to upload it to a Google Drive.  Please let me know if you have trouble accessing it.

Google Drive - Sample File