Any Suggests on Creating a Slicer Showing Loss of Business??

[IamTDR]

Hi
I am seeking suggestions/ideas on completing a requested task.
See image below.

Client would like to have a slicer that a end user could then select and see products that declined after initially having sold 10 or more products in a fiscal year.

So from my report, Product A in Fiscal Year 2017 Sold 68 units.  2018 Sold 211 so this would be labeled 'Business Still Exist'. Then in Fiscal Year 2019 the Units dropped below 10 so the label would be 'Loss of Business' but then in Year 2020 Units are back to Existing again.

Really not sure if this request is possible.  Each Product and year have multiple variables.
Any suggestions on how to proceed?

[MohammadLoran25 · Accepted solution]

Hi @IamTDR ,

Follow steps below:

Assume you have a DateTable.

1-Create a measure:

Product Quantity Sold = SUM(ProductSales[SalesQty])

2-Create a measure to calculate growth compared to previous year:

GrowthMasure = 
VAR _CurrentYear=MAX(DateTable[Year])

VAR _PreviousYear=_CurrentYear-1

VAR _CurrentYearSale=CALCULATE([Product Quantity Sold],FILTER(ALL(DateTable), DateTable[Year]= _CurrentYear))
VAR _PrevYearSale=CALCULATE([Product Quantity Sold],FILTER(ALL(DateTable), DateTable[Year]= _PreviousYear))

VAR _Growth=DIVIDE(_CurrentYearSale-_PrevYearSale,_PrevYearSale)

RETURN _Growth

3-Create a Table as below with the name 'StatusOfBusiness' :

StatusName	MinOfRange	MaxOfRange
Loss Of Business	-2	1
Normal Business	1	2
Business Still Exist	2	3

You can change the records based on your need.

4-Create a Status Measure :

StatusMeasure =
CALCULATE (
    MIN ( StatusOfBusiness[StatusName] ),
    FILTER (
        StatusOfBusiness,
        [GrowthMasure] >= StatusOfBusiness[MinOfRange]
            && [GrowthMasure] < StatusOfBusiness[MaxOfRange]
    )
)

Then you can do it in diffrent ways:

1-Put this measure in your visual directly and then Put the status name field from the table we made as slicer.

2-Instead of putting this measure in your visual directly, you can put it in filterpane as a filter and set it to your desired value. For instance, "Loss of Business"

If this answer solves your problem, give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran