Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm new to the forums so forgive me if this isn't explained that well. I'm trying to work out how it would be possible to show businesses whose sales are on a downward trend.
I have sales information for a large number of businesses and I use a measure to display their sales over the previous 12 months (12 month total sales) by month. What I would like to do is to identify those that have had declining sales for three straight months. I currently display the 12 month total sales in a table but have no way of sorting out the businesses in decline.
I've popped some dummy data in below. I don't know how to achieve it but I would like to be able to filter Bravo and Delta out of the data below.
12month total sales | |||
Business Name | Jan-17 | Feb-17 | Mar-17 |
Alpha | 1000 | 1200 | 1000 |
Bravo | 1500 | 1400 | 1300 |
Charlie | 1200 | 1100 | 1200 |
Delta | 400 | 300 | 250 |
Echo | 1000 | 1000 | 1000 |
Foxtrot | 1000 | 1200 | 1400 |
Thanks in advance for your help. Let me know if you need anything else.
Solved! Go to Solution.
Hi @arcadefire4,
If I understand you correctly, you should be able to follow steps below to sorting out the businesses in decline.
I assume you have a table called 'Table1' like below.
1. Use the formula below to create a new calculate column in 'Table1'.
Is In Decline = VAR previousMonthSales = CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Business Name] ), PREVIOUSMONTH ( Table1[Month] ) ) VAR NextMonthSales = CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Business Name] ), NEXTMONTH ( Table1[Month] ) ) RETURN IF ( Table1[Sales] < previousMonthSales && Table1[Sales] > NextMonthSales && NextMonthSales <> 0, 1, 0 )
2. Then you should be able to use the new create calculate column as Slicer or visual level filter to sorting out the businesses in decline.
Here is the sample pbix file for your reference.
Regards
Hi @arcadefire4,
If I understand you correctly, you should be able to follow steps below to sorting out the businesses in decline.
I assume you have a table called 'Table1' like below.
1. Use the formula below to create a new calculate column in 'Table1'.
Is In Decline = VAR previousMonthSales = CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Business Name] ), PREVIOUSMONTH ( Table1[Month] ) ) VAR NextMonthSales = CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Business Name] ), NEXTMONTH ( Table1[Month] ) ) RETURN IF ( Table1[Sales] < previousMonthSales && Table1[Sales] > NextMonthSales && NextMonthSales <> 0, 1, 0 )
2. Then you should be able to use the new create calculate column as Slicer or visual level filter to sorting out the businesses in decline.
Here is the sample pbix file for your reference.
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |