Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors