cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajat_1284
Helper I
Helper I

Slicer on a Measure

Hi,

I have data like

 

Name       Sales       Quarter

A               100          2020Q1

A               250          2020Q2

B                50            2020Q1

B                 75           2020Q2

 

I want to have a quarter to quarter filter on the data so only that data is displayed where the Q on Q is > my given number

 

So in case i give quarter on quarter as say greater than 25, the rows for the column B should not be displayed

 

Only onw row should be displayed as

 

emp  qoq_Sales

A       150 

 

since i won't be having quarter displayed in the final table.

 

I have created a measure which calculates sum of the current quarter and subtracts the sum of the previous quarter. I only need the difference between the current quarter and the previous quarter and that is already done using the time intelligence function.

I can see the quarter on quarter value in my table, it's just filtering it doesn't seem to work for me.

 

 

 

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

Hi @rajat_1284 ,

 

Check the formula.

Measure = 
var _year = LEFT(SELECTEDVALUE('Table'[Quarter]),4)
var _month = RIGHT(SELECTEDVALUE('Table'[Quarter]),1)
var _previous = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Name]=SELECTEDVALUE('Table'[Name])&&LEFT('Table'[Quarter],4)=_year&&RIGHT('Table'[Quarter],1)+0=_month-1))
var _diff = IF(ISBLANK(_previous),0,SUM('Table'[Sales])-_previous)
return
_diff

6.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

25 is just an idea. I want the user to select any value, and that is why i wanted a slicer.  I tried the whatifparameter, it works but since my value is -100 billion to + 100 billion, the interval needs to be higher in the generate series.

 

What i wanted effectively is, that based on the measure, which is a calculation of Current quarter - previous quarter

 

i wanted all calculation values to be saved in a separate table, and then i could find out the min and the max of those values and get myself a slicer based on it. It would help me in reducing the series limitations.

There should be a better way for this.

 

Hi @rajat_1284 ,

 

If you want a visual, you could modify the measure as below.

Measure = 
var _year = LEFT(SELECTEDVALUE('Table'[Quarter]),4)
var _month = RIGHT(SELECTEDVALUE('Table'[Quarter]),1)
var _previous = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Name]=SELECTEDVALUE('Table'[Name])&&LEFT('Table'[Quarter],4)=_year&&RIGHT('Table'[Quarter],1)+0=_month-1))
var _diff = IF(ISBLANK(_previous),0,SUM('Table'[Sales])-_previous)
return
if(_diff>selectedvalue(slicer),1,0)

Then add the measure to visual filter value =1.

If you want a table, I'm affraid it's not supported to create a table by selected value in slicer.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Ashish_Mathur
Super User III
Super User III

You should be able to filter the quarterly difference measure by >25.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

It is just for example, it has to be through a slider option or a user input. 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors