Forum Discussion

arnie's avatar
arnie
Icon for Helper I rankHelper I
1 year ago

How to toggle with slicer in Power BI

Hi Everyone,

I have a table which is shown below for FY25.

 

I have a table called Selected_Measure_2, in which I would like to use to toggle between "Grand Total" and "YTD Total".

 

How do I set it up so that when "YTD Total" is selected in the slicer, the matrix above displays data from Jul 24 to MMM YY?

Also, when "Grand Total" is selected in the slicer, the matrix above displays the whole Financial Year?

 

My DAX is also provided, just in case it needs tweaking.

 

_Calc. Budget =
VAR CurrentDate = TODAY()
VAR CurrentMonthYear = FORMAT(CurrentDate, "YYYYMM")
VAR StartMonthYear = "202407"

-- Check the level of the calculation
VAR IsRowLevel =
    ISINSCOPE(SalesData[Product Type]) ||
    ISINSCOPE(SalesData[Order Group]) ||
    ISINSCOPE(SalesData[Sales Class Alt.]) ||
    ISINSCOPE(SalesData[Region]) ||
    ISINSCOPE(SalesData[Market])

VAR IsColumnLevel = ISINSCOPE('Date'[Year-Month Code])

-- Calculate actual and budget values for the current Year-Month Code
VAR ColumnDate = MAX('Date'[Year-Month Code])

VAR ActualTonnesForMonth =
    CALCULATE(
        [Tonnes Actual],
        'Date'[Year-Month Code] = ColumnDate,
        'Date'[Year-Month Code] >= StartMonthYear,
        'Date'[Year-Month Code] <= CurrentMonthYear
    )

VAR BudgetTonnesForMonth =
    CALCULATE(
        [Tonnes Budget],
        'Date'[Year-Month Code] = ColumnDate,
        'Date'[Year-Month Code] > CurrentMonthYear
    )

-- Calculate actual and budget values for all Year-Month Codes in the row's scope
VAR ActualTonnesForRow =
    CALCULATE(
        [Tonnes Actual],
        'Date'[Year-Month Code] >= StartMonthYear,
        'Date'[Year-Month Code] <= CurrentMonthYear,
        REMOVEFILTERS('Date'[Year-Month Code])
    )

VAR BudgetTonnesForRow =
    CALCULATE(
        [Tonnes Budget],
        'Date'[Year-Month Code] > CurrentMonthYear,
        REMOVEFILTERS('Date'[Year-Month Code])
    )

-- Total values for columns or rows
VAR TotalForMonth = ActualTonnesForMonth + BudgetTonnesForMonth
VAR TotalForRow = ActualTonnesForRow + BudgetTonnesForRow

-- Grand Total Calculation
VAR GrandTotal =
    CALCULATE(
        ActualTonnesForRow + BudgetTonnesForRow,
        REMOVEFILTERS('Date'[Year-Month Code]),  -- Remove filters for Year-Month Code
        REMOVEFILTERS(SalesData)  -- Remove filters for SalesData
    )

-- Determine the value based on context
RETURN
IF(
    IsRowLevel && IsColumnLevel,
    -- At the intersection of row and column (specific Year-Month Code and row level)
    IF(
        ColumnDate <= CurrentMonthYear && ColumnDate >= StartMonthYear,
        [Tonnes Actual],
        IF(
            ColumnDate > CurrentMonthYear,
            [Tonnes Budget],
            BLANK()
        )
    ),
    IF(
        IsRowLevel,
        -- Subtotal for rows (aggregate across all months for the row's scope)
        TotalForRow,
        IF(
            IsColumnLevel,
            -- Subtotal for columns (aggregate across all rows for the column's scope)
            TotalForMonth,
            -- Grand total (bottom-right cell)
            GrandTotal
        )
    )
)
 

Thanks,

Arnie.

 

 

Need Help

1 Reply

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    1 year ago

    Hi arnie ,
    To set up a toggle slicer in Power BI so that selecting "YTD Total" or "Grand Total" dynamically adjusts the matrix, follow these steps:

    Steps to Implement:

    1. Create a Measure for Conditional Display Modify or create a measure that evaluates the slicer selection and adjusts the data displayed based on the selection.
      Here’s how to create the toggle functionality:
      • Assume the slicer uses the View column from the Selected_Measure_2 table.
      • Use the following DAX to create a conditional measure
        Toggle Total =
        VAR CurrentSelection = SELECTEDVALUE('Selected_Measure_2'[View])
        VAR CurrentDate = TODAY()
        VAR CurrentMonthYear = FORMAT(CurrentDate, "YYYYMM")
        VAR StartMonthYear = "202407" -- Replace this with the start of your financial year

        VAR YTDTonnes =
        CALCULATE(
        [_Calc. Budget],
        'Date'[Year-Month Code] >= StartMonthYear,
        'Date'[Year-Month Code] <= CurrentMonthYear
        )

        VAR GrandTonnes =
        CALCULATE(
        [_Calc. Budget],
        'Date'[Year-Month Code] >= StartMonthYear
        )

        RETURN
        SWITCH(
        TRUE(),
        CurrentSelection = "YTD Total", YTDTonnes,
        CurrentSelection = "Grand Total", GrandTonnes,
        BLANK()
        )
    2. Adjust the Matrix to Use the New Measure Replace your existing measure in the matrix visual with the Toggle Total measure.

    3. Set Up the Slicer

       

      • Add a slicer to your report and bind it to the View column from the Selected_Measure_2 table.

      • Ensure it displays the options YTD Total and Grand Total.

    4. Format the Matrix for Correct Display

       

      • Ensure your matrix rows and columns are configured with fields like Product Type, Order Group, and Year-Month to properly summarize the data.

    Explanation of the Logic:

    • YTD Total: The measure filters the data from the start of the financial year (StartMonthYear) up to the current month (CurrentMonthYear).
    • Grand Total: The measure displays the full-year data without restricting the date range.

    Testing:

    • Select "YTD Total" in the slicer to ensure only data up to the current month (e.g., Jul 24 to the current MMM YY) is displayed.
    • Select "Grand Total" to ensure the full financial year is displayed.

    If adjustments are needed for the _Calc. Budget measure or for the financial year logic, update the respective sections in the Toggle Total DAX formula.
    If I have resolved your question, please consider marking my post as a solution. Thank you!