Default value in a KPI visual when no slicer selected

[maurcoll]

Hi 

I need some help please

I have a KPI visual where the value is total sales for the current 12 month period, the target is the total sales for the previous 12 month period. I want to know is it possible to show the sales for a default value when nothing is selected in the slicer for product name. So if nothing is selected it defaults to total sales for Product A but if something is selected then it shows the sales for the selected item. 

This is the measure i am using for current 12 months

VAR maxDate = TODAY() - DAY(TODAY())

VAR minDate = EDATE(TODAY() - DAY(TODAY()), -12)

Return

CALCULATE('Table'[Total Sales],  

'Calendar'[Date] <= maxDate && 'Calendar'[Date] > minDate)

[ExcelMonke · Accepted solution]

Hello,

You can consider using the function ISFILTERED. So something like:

VAR _ProductA = CALCULATE( [ Your measure ], Table[Product] = "ProductA")

RETURN
IF(
ISFILTERED(Table[Product]),[ Your measure ], _ProductA)

Effectively, you are checking if you are filtering for a product. If you are not filtering, then it will calculate your function for ProductA, else just return your measure. 

[DataNinja777 · Accepted solution]

Hi @maurcoll ,

You can create your required output by using if statement combined with the isfiltered function in your DAX measures. This allows the measure to check if the slicer has an active selection. If it doesn't, it defaults to "Product A".

You need to apply this logic to both your main value (TTM) and your target (PTTM).

KPI Value (Sales TTM) =
VAR maxDate = TODAY() - DAY(TODAY())
VAR minDate = EDATE(TODAY() - DAY(TODAY()), -12)
VAR SlicerIsFiltered = ISFILTERED('Product'[ProductName])

RETURN
IF(
    SlicerIsFiltered,
    // Slicer is used: Calculate for selection
    CALCULATE(
        [Sales],
        'Calendar'[Date] <= maxDate && 'Calendar'[Date] > minDate
    ),
    // Slicer is empty: Default to "Product A"
    CALCULATE(
        [Sales],
        'Calendar'[Date] <= maxDate && 'Calendar'[Date] > minDate,
        'Product'[ProductName] = "Product A"
    )
)

KPI Target (Sales PTTM) =
VAR current_maxDate = TODAY() - DAY(TODAY())
VAR current_minDate = EDATE(current_maxDate, -12)
VAR prev_maxDate = current_minDate
VAR prev_minDate = EDATE(current_minDate, -12)
VAR SlicerIsFiltered = ISFILTERED('Product'[ProductName])

RETURN
IF(
    SlicerIsFiltered,
    // Slicer is used
    CALCULATE(
        [Sales],
        'Calendar'[Date] <= prev_maxDate && 'Calendar'[Date] > prev_minDate
    ),
    // Slicer is empty: Default to "Product A"
    CALCULATE(
        [Sales],
        'Calendar'[Date] <= prev_maxDate && 'Calendar'[Date] > prev_minDate,
        'Product'[ProductName] = "Product A"
    )
)

You can use these two measures in your KPI visual's "Indicator" and "Target" fields.

The resultant output is as shown below:

I have attached an example pbix file for your reference.

Best regards,

[ExcelMonke · Accepted solution]

Hello,

You can consider using the function ISFILTERED. So something like:

VAR _ProductA = CALCULATE( [ Your measure ], Table[Product] = "ProductA")

RETURN
IF(
ISFILTERED(Table[Product]),[ Your measure ], _ProductA)

Effectively, you are checking if you are filtering for a product. If you are not filtering, then it will calculate your function for ProductA, else just return your measure.