Combining 3 Measures into a Single Measure

[norken20]

Hi All,

I'm not sure if this is possible. Do you think I can combine these 3 measures below into a single one so that once I inputted them in the Line graph, this will be a single line only and 3 different slicers work for it.

YEARLYMUTARGET = TOTALYTD(SUM('MU Target'[Value]), DIMDATE[Date], ALL('Extract'[Estimated Close Date]), "8/31")+0

YEARLYOGTARGET = TOTALYTD(SUM('OG Target'[Value]), DIMDATE[Date], all('Extract'[Estimated Close Date]), "8/31")+0

YEARLYTARGET = TOTALYTD(SUM('Yearly Target'[Revenue]), DIMDATE[Date], all('Yearly Target'[Date]), "8/31")+0

Individually they work but I'm not  sure how can I combine them into one so it will work as one.

Note that the common relationship between them is the DIMDATE[Date] and Estimated Close date. They from 3 different tables.

Hope there could be a possible solution on this. Thank you 🙂

[PaulDBrown · Accepted solution]

@norken20 

You can try following this method.

1) create a new table (I've called it "Select Measure") using the "Enter Data" option in the ribbon under the Home tab. Type in the names of your measures (and optionally an index column for ranking purposes). In my example, I'm creating a table to be able to select bewteen the measures "Sales 2018", "Target 2018", "Sales 2019", and "Target 2019".

 2) Create new measures for each of your meaures to be used in your line chart visual following this structure for each:

Sales 2018 (Single Sel) = IF(SELECTEDVALUE('Select Measure'[Selected Measure]) = "Sales 2018"; 
                            [Sales 2018]; 
                            BLANK())

 3) Add these new measures to the Values bucket in your line visual:

4) create a slicer using the new table you have created (with the measure names) and in the formatting pane for the slicer turn on th "single select" option:

And you get this end result:

If you need to show more than one measure at a time in the line graph, we need to tweak the measures. Let us know if you wish to do so.

[amitchandak]

As of now better to sum like this

Target = TOTALYTD(SUM('MU Target'[Value]), DIMDATE[Date], ALL('Extract'[Estimated Close Date]), "8/31")+0
+ TOTALYTD(SUM('OG Target'[Value]), DIMDATE[Date], all('Extract'[Estimated Close Date]), "8/31")+0
+ TOTALYTD(SUM('Yearly Target'[Revenue]), DIMDATE[Date], all('Yearly Target'[Date]), "8/31")+0

[norken20]

Hi @amitchandak 

Thanks! Your solution work but what if I want the value of the Yearly Target will be displayed initially on the graph and not the total sum of the 3? The slicers work now but just the initial display on is the value of the below. Is there any way we can do this?

TOTALYTD(SUM('Yearly Target'[Revenue]), DIMDATE[Date], all('Yearly Target'[Date]), "8/31")+0

 Thanks!

[amitchandak]

Not sure I got that. But if you want slicer to change the values in visual you can refer:https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50....

You can create a couple measure or change logic based on selection

[norken20]

Hi @amitchandak,

Apologies for the confusion. What I'm trying to say is that the 3 measures are from different table so for the Yearly Target will be the initial view. Then once we used slicer, the value will change accordingly on the value chose on the slicer. We don't need to add them as initially. Hope this is clear now. Thanks 🙂

[amitchandak]

Slicer should work. Still not very clear