Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Bobbys
Helper III
Helper III

Slicer for Units/Value and YTD/MAT

Hello,

I'd like to create a slicer based on my data and measures.
I'd like there to be a slicer for value aswell as for unit. Here is how my dashboard currently looks. 

 

Bobbys_0-1633977763281.png

 

I'd like the option to pick Units, and the same charts and calculations would be made. 
Also, I would like to have the option to pick between YTD and MAT calculations. As seen from the screenshot, currently my dashboard is showing YTD, but I have made a MAT measure aswell, which I would like to enable using a slicer to switch between YTD and MAT.

 

Any idea how I can do this? Would appreciate any help. Thanks. 

6 REPLIES 6
Anonymous
Not applicable

Hi  @Bobbys ,

I created some data:

vyangliumsft_0-1634799739047.png

Here are the steps you can follow:

1. Click Enter data to create a table Dynamic.

vyangliumsft_1-1634799739049.png

2. Create measure.

Sales_YTD =
CALCULATE(SUM('amount_table'[sales]),FILTER(ALL('amount_table'),'amount_table'[date]>=DATE(YEAR(TODAY()),1,1)&&'amount_table'[date]<=TODAY()))
Sales_Mat = CALCULATE(SUM('amount_table'[sales]),FILTER(ALL('amount_table'),'amount_table'[date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)&&'amount_table'[date]<=TODAY()))
Units_YTD =
CALCULATE(SUM('amount_table'[units]),FILTER(ALL('amount_table'),'amount_table'[date]>=DATE(YEAR(TODAY()),1,1)&&'amount_table'[date]<=TODAY()))
Units_Mat = CALCULATE(SUM('amount_table'[units]),FILTER(ALL('amount_table'),'amount_table'[date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)&&'amount_table'[date]<=TODAY()))
Slice =
SWITCH(
    SELECTEDVALUE('Dynamic'[Measure Name]),
    "Sales_YTD",[Sales_YTD],
    "Sales_Mat",[Sales_Mat],
    "Units_YTD",[Units_YTD],
    "Units_Mat",[Units_Mat])

3. Result:

vyangliumsft_2-1634799739051.png

Does this match your expected result.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

That's brilliant @Anonymous. 
Now, I might sound like a pain in the ass, but is there any way I can implement the previous year's YTD and MTD, when clicking on the slicer?

An example: 

Bobbys_0-1634801392825.png

 

So by clicking "Sales YTD" slicer, the table also updates with the YTD -1 numbers. And when I'd pick "Sales MAT", it would show Sales MAT and Sales MAT - 1. I have all these measures already calculated, so I have the numbers. I'd just like the table to update itself with the previous period of the measure picked in the slicer

 

Thanks again!!

Anonymous
Not applicable

Hi  @Bobbys ,

I created some data:

vyangliumsft_0-1634716548158.png

Here are the steps you can follow:

1. Click Enter data to create a table Dynamic.

vyangliumsft_1-1634716548162.png

2. Create measure.

Mat = 
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)&&'Table'[date]<=TODAY()&&'Table'[group]=MAX('Dynamic'[group])))
YTD =
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]>=DATE(YEAR(TODAY()),1,1)&&'Table'[date]<=TODAY()&&'Table'[group]=MAX('Dynamic'[group])))
Measure_Slice =
SWITCH(
    SELECTEDVALUE('Dynamic'[Measure Name]),
    "YTD",[YTD],
    "Mat",[Mat])
Flag =
IF(
    MAX('Table'[group])=SELECTEDVALUE('Dynamic'[group]),1,0)

3. Place [group] and [Measure Name] of the Dynamic table into the slicer

vyangliumsft_2-1634716548165.png

4. Place [Flag] in the Filter of the table visual object, set is = 1, and apply filter.

vyangliumsft_3-1634716548167.png

5. Result:

vyangliumsft_4-1634716548169.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hello @Anonymous, thanks for the response.

In your table, the value for volume and sales value are in the same column, meanwhile I have one collumn for sales and one for volume. Therefore your DAX for YTD and MAT won't work on my data. This is how my table looks: 

Bobbys_0-1634729764418.png

 

I appreciate your help! 

Anonymous
Not applicable

Hi  @Bobbys ,

I created some data:

vyangliumsft_0-1634179290839.png

[Mat] and [YTD] are two measures.

Here are the steps you can follow:

1. Click Enter data to create a table Dynamic.

vyangliumsft_1-1634179290840.png

2. Create measure.

Measure_Slice =
SWITCH(
    MIN('Dynamic'[Measure ID]),
    "YTD_Slice",[YTD],
    "MAT_Slice",[Mat])

3. Use [Measure_Name] as the slicer

4. Result:

When the slicer selects Mat, the value of Measure[Mat] is displayed.

vyangliumsft_2-1634179290843.png

When the slicer selects YTD, the value of Measure[YTD] is displayed.

vyangliumsft_3-1634179290847.png

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Absolutely brilliant @Anonymous.
Is there any way I can have this for units aswell?
So it would look something like this:

* Sales
   * YTD
   * MAT

* Units
  * YTD
  * MAT

It would pretty much be that sales and units would be the slices, and then YTD or MAT would be the slicer inside the slicer again. Any way to get this?

Best regards and thanks a lot

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.