how to filter by measure range

[Anonymous]

I have a simple sales data model.  I have a measure like Total Sales = SUM('Fact Sales'[Sale Amount]).  This measure appears as a Value on a matrix visual, where the dimension is 'Dimension Employee'[Employee Name].  I want to filter this matrix visual by pre-defined ranges of Total Sales that exist in a slicer.  So, I have created a disconnected table ('Total Sales Range Values') with my range options:

Total Sales Range

5,000+

1,000 - 4,999

0 - 999

How can I filter this matrix visual as described?  Note: If no slicer value is checked, then all rows should return in the matrix visual.

[Anonymous · Accepted solution]

Hi @Anonymous ,

If the range column is in text format, then you will need to create new columns for min value and max value in number format.

Such as below.

Then create a measure and add it to visual filter.

Measure = 
var _min = SELECTEDVALUE(slicer[min])
var _max = IF(ISBLANK(SELECTEDVALUE(slicer[max])),9999,SELECTEDVALUE(slicer[max]))
return
IF([totalsales]>=_min&&'Table'[totalsales]<=_max,1,0)

Result would be shown as below.

Best Regards,

Jay

[Tahreem24]

Create a calculated column for sales range like below:

Range Column = Switch(Total Sales, Total Sales>0 && Total Sales <999, "0-999", Total Sales>1000 && Total Sales <4999, "1000-4999", Total Sales > 5000,"5000") 

And use this in slicer. 

[Anonymous]

@Tahreem24 

Total Sales is a measure.  How can I use a measure in a calculated column?