Forum Discussion
Combining 3 Measures into a Single Measure
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.
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.
6 Replies
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.
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")+0Hi 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")+0Thanks!
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/500115.
You can create a couple measure or change logic based on selection
