Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Thanks,
Arnie.
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:
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()
)
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.
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:
Testing:
User | Count |
---|---|
121 | |
72 | |
71 | |
57 | |
50 |
User | Count |
---|---|
167 | |
83 | |
68 | |
65 | |
55 |