cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic Measures based on filter selection

I have Sales Column and I created a YTD measure using that column. I want to accomplish the following.

 

Show YTD or Sum of Sales when a date value is selected. Is there a way to accomplish this, like create a dummy filter(like groupby) with values YTD and Monthly and when date value is selected with one of these group by filter values it would select corresponding value. For Ex in my case when YTD is selected I want see YTD measure and sum of sales when Monthly is selected.

 

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@Anonymous

 

1. Create a New Table with

 

Calc

YTD

Monthly

 

2. Then Use a Slicer with this column

 

3. Create a measure

 

Sales=Switch(Values(Newtable[Calc]),"YTD",MeasureofYTD,"Monthly",MeasureofSumSales)

 

 




Lima - Peru

View solution in original post

2 REPLIES 2
KHorseman
Community Champion
Community Champion

I just finished a straight hour of writing a series of measures that do exactly this. Here's my method:

 

Use "Enter Data" to create a new table. Call it something like "MeasureBehavior" and make a column called "Stat". You can call it whatever you want, but that's what I call it. Stat is the name of the method you want to use, so Monthly, YTD, whatever else...

 

Then your measures will take the following form:

 

Measure = IF(
	HASONEVALUE(MeasureBehavior[Stat]),
	SWITCH(
		FIRSTNONBLANK(MeasureBehavior[Stat], 1),
		"Monthly", SUM(TableName[ColumnName]),
		"YTD", CALCULATE(
			SUM(TableName[ColumnName]),
			DATESYTD(DateTable[Date])
		),
		BLANK()
	),
	BLANK()
)

The BLANK() functions are defaults for when conditions are not met. The first one is if there is only one stat selected but it's not either Monthly or YTD. This condition should never actually happen but I think it's best to give SWITCH a failsafe blank as a general rule. The last BLANK() is for cases when more than one stat is selected (or when the slicer has no selection, which is the same thing). You may prefer to use the plain monthly sum here, so the default will always be monthly unless YTD is explicitly selected. Up to you.

 

Oh, then of course you would add a slicer for MeasureBehavior[Stat] to the report.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Vvelarde
Community Champion
Community Champion

@Anonymous

 

1. Create a New Table with

 

Calc

YTD

Monthly

 

2. Then Use a Slicer with this column

 

3. Create a measure

 

Sales=Switch(Values(Newtable[Calc]),"YTD",MeasureofYTD,"Monthly",MeasureofSumSales)

 

 




Lima - Peru

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.