Dynamic Filter with Dynamic Measure Selection in Report Matrix

[pacificnwp]

I have a solution that "works", but I don't think it's ideal and I don't fully understand why it works.

I have a matrix with names going down, and year-quarter going across.  I want the ability to show Revenue or Percent of Sales Goal based on a filter at the top of the report.

We have two different products and the respective values are stored in the same row - name, revenue, percent of sales - there are different measures for those products already.

I created a separate table with the Product 1, Product 2, and Total, and tied those to a slicer.  I then created a User Selection field (

User Selection = SELECTEDVALUE('Dynamic Slicers'[Measure Type]) ).  I then created a dynamic field for the Percentage of Sales - 

Percent of Sales =

SWITCH(

    TRUE(),

    [User Selection] = "Coronary", [Product 1 Attainment Calc],

    [User Selection] = "Peripheral", [Product 2 Attainment Calc],

    [User Selection] = "Total", [Total Percent of Sales Calc],

    BLANK()

)

This solution worked out and the report functioned as expected.  However, a request was made to include revenues.  Following some tutorials, I created a calculation group called "Metric Format" with Percent of Sales and Revenue with the exact same logic as above (except the revenue measure uses revenue fields).  

I then added another slicer to my report based on that Metric Format, and it works.  However, my rub is that it works no matter which measure I stick in the matrix, i.e., my "dynamic" fields that I initially created seem pointless now, and perhaps even misleading.  Is that okay, or is there a better way to go about this?

My first solution prior to this the following (and leveraged another "enter data" type of table), however the matrix wouldn't update the data type correctly, i.e., dollar amounts didn't show as dollar amounts.  I would be open to continuing this method as well if there's a solution to get the data types to display correctly.

Dynamic Metric Selection =

SWITCH(

  TRUE(),

  [User Product Selection] = "Product 1" && [User Metric Selection] = "Attainment", [Product 1 Attainment Calc],

  [User Product Selection] = "Product 1" && [User Metric Selection] = "Revenue", [Product 1 Sales Calc],

[User Product Selection] = "Product 2" && [User Metric Selection] = "Attainment", [Product 2 Attainment Calc],

[User Product Selection] = "Product 2" && [User Metric Selection] = "Revenue",     [Product 2 Sales Calc],

[User Product Selection] = "Total" && [User Metric Selection] = "Attainment",      [Total Attainment Calc],

[User Product Selection] = "Total" && [User Metric Selection] = "Revenue",          [Total Sales Calc],

BLANK()  // default for no selection

)

Hopefully that all makes sense, thanks!

[DanieleUgoCopp · Accepted solution]

I think a better approach is to choose one pattern and stick with it, and calculation groups are probably the better option here.

What you’re seeing now actually makes sense once the calculation group is in place, it applies its logic to whatever measure is in the matrix.
That’s why the original dynamic SWITCH measures feel pointless  they’re effectively being bypassed, and that’s expected behavior.

A simpler setup would be to keep very basic measures that do only one thing, for example:

Base Revenue := SUM ( Sales[Revenue] )
Base Attainment := DIVIDE ( SUM ( Sales[Actual] ), SUM ( Sales[Target] ) )

Then let a calculation group handle the choice between Revenue and Attainment using SELECTEDMEASURE(), including the correct formatting for currency versus percent.
In the matrix you only need one base measure, and the slicer on the calculation group choose what the user sees.

I think this should be better mainly because it avoids duplicated logic and fixes the formatting issue in a more natural way, even though your current solution does technically work.

[pacificnwp]

Thanks I'll do that!

[amitchandak]

@pacificnwp , You want to switch the format, you have to dynamic string formatting and almost the same Switch you have right format 

https://www.sqlbi.com/articles/introducing-dynamic-format-strings-for-dax-measures/

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings

In the format part measure, something like this, chnage as per need 

SWITCH(
    TRUE(),

    [User Metric Selection] = "Revenue",
        "$#,##0.00;($#,##0.00)",

    [User Metric Selection] = "Attainment",
        "0.0%",

    "#,##0"
)

[pacificnwp]

Thanks - I proceeded with the calculation group method, however I don't like that it removed implicit measures from my data set.  I then tried to implement your solution.  I was able to get it to work for my matrix, however the formatting converted the values to text, which didn't work for my line chart.  I have both a line chart and a matrix in my report that leverage these fields. 

Is it possible to do this while preserving the data types?