Plot multiple measures against a 'single' value of slicer selection

[Anonymous]

Hi all,

I have a requirement to plot 2 measures (actual and forecast) on Y axis in a line chart against each month. I have total of 6 measures that belong to three categeries. Something like below.

X (actual XX, forecast XX)

Y actual YY, forecast YY)

Z (actual ZZ, forecast ZZ)

I need to use a slicer with 3 options to select from however, when I select any one from above, the graph should dynamically plot the 2 measures in the selected category. For instance, I select option 'X', the line graph should plot actuals and forecast (2 measures) against the date (month) on the x axis. I went through many posts and found solution to have ONE measure plotted against ONE selection but could not find where I could plot MULTILPE measures (in my case 2) for a single selection in the slicer. Any help will be greatly appreciated.

Thanks.

[richbenmintz · Accepted solution]

Hi @Anonymous ,

Essentially what you need to do is created a disconnected dimension that provides the users with a measure type to select, in your case {X,Y,Z}, then create a actual and forecast measure that returns the appropriate value based on the user selection, looking something like:

Actual = 
    IF(HASONEVALUE('metric selection'[Metric]),
    SWITCH(
            VALUES('metric selection'[Metric]), 
            "X", SUM(metrics[actual x]),
            "Y", SUM(metrics[actual y]),
            "Z", SUM(metrics[actual z])
), BLANK())

Forecast = 
    IF(HASONEVALUE('metric selection'[Metric]),
    SWITCH(
            VALUES('metric selection'[Metric]), 
            "X", SUM(metrics[Forecast x]),
            "Y", SUM(metrics[Forecast y]),
            "Z", SUM(metrics[Forecast z])
), BLANK())

You will find a sample pbix file here sample.pbix 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

Thanks @richbenmintz! Your solution was simple and elegant and solved my problem. Thanks so much!

[richbenmintz]

Hi @Anonymous,

Happy to help

[amitchandak]

@Anonymous , not very clear. Refer measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://www.youtube.com/watch?v=vlnx7QUVYME

[parry2k]

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490