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
Super User

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.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!