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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
v-yangliu-msft
Community Support
Community Support

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 @v-yangliu-msft
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!!

v-yangliu-msft
Community Support
Community Support

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 @v-yangliu-msft, 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! 

v-yangliu-msft
Community Support
Community Support

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 @v-yangliu-msft.
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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors