how to use measure which is in percentage format in slicer

[Anonymous]

Hi All,

I have the data in below format and i need to provide user a slicer for selecting Rate  (last column in below table). This should be a Range slicer as shown below. But currently, slicer is not supporting a measure. Any alternative that i can use or achieve this?

source_system	orderstatus	Filled-Cosed count	Count of Orders	Total Orders	Rate (Filled- Cosed count / Total Orders)
Amrutha	Cancelled		45959	56405
Amrutha	Filled	18203	18203	56405	32.27%
Frank	Closed	19745	19745	19941	99.02%
Frank	Halted		14	19941
Frank	Submitted		215	19941
LS			3356	14472
LS	Cancelled		10119	14472
LS	Closed	36	36	14472	0.25%
LS	Deleted		3	14472
LS	Filled	12856	12856	14472	88.83%
LS	Open		2410	14472
LS	Pending		79	14472
LS	Scheduled		29	14472
LL	Cancelled		10	317
LL	Closed	93	93	317	29.34%
LL	Filled	211	211	317	66.56%
LL	Open		2	317
LL	Pending Fill		1	317
SW	Filled	435707	435707	435707	100.00%

[Ashish_Mathur]

Hi,

Instead of the allowing the user to select any value in the slicer visual, will you be OK with the slicer showing buckets i.e. 0%-10%, 11%-15% etc.  If yes, then share the link from where i can download your PBI file and also share the buckets that you would like to see in the slicer. 

[Anonymous]

@Ashish_Mathur  Thanks for the suggestion. but to my bad luck, the client requires it as a slicer.

[Ashish_Mathur]

Hi,

It will be a slicer.  It is just that your client will not be able to select 32.6% but instead will be able to select a bracket like 30% - 35%.  Will that be OK?

[Anonymous]

@Ashish_Mathur  Thanks Ashish, 

That would be fine i guess.  I cant share the file here as i dont have the option.

Will you be able to create a file with test file and the slicer and share with some dummy data.

The columns that i have are

Source, OrderID, Orderstatus, Specialty, SubSpecialty, Bill,  Fill Rate

The bucket can of 5% increments.

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

Hi @Anonymous 

I build a sample for you to solve this problem. (Rate column is a calculated column)

Rate = 'Table'[Filled-Cosed count]/'Table'[Total Orders]

Firstly build a Slicer, you can build a calculated Table directly or build a Whar if parameter like replies above.

Slicer = GENERATESERIES(0,1,0.0001)

Then build a measure to achieve your goal.

Here are two different measures for you.

Measure1 = 
VAR _Max =
    MAX ( Slicer[Percent] )
VAR _Min =
    MIN ( Slicer[Percent] )
VAR _Maxx =
    MAXX ( ALL ( slicer ), Slicer[Percent] )
RETURN
    IF (
        _Max = _Maxx,
        IF (
            AND ( SUM ( 'Table'[Rate] ) >= _Min, SUM ( 'Table'[Rate] ) <= 1 ),
            SUM ( 'Table'[Rate] ),
            BLANK ()
        ),
        IF (
            AND ( SUM ( 'Table'[Rate] ) >= _Min, SUM ( 'Table'[Rate] ) <= _Max ),
            SUM ( 'Table'[Rate] ),
            BLANK ()
        )
    )

Measure 2 = 
VAR _Max =
    MAX ( Slicer[Percent] )
VAR _Min =
    MIN ( Slicer[Percent] )
VAR _Maxx =
    MAXX ( ALL ( slicer ), Slicer[Percent] )
VAR _a =
    CALCULATE (
        SUM ( 'Table'[Rate] ),
        FILTER ( 'Table', 'Table'[Rate] >= _Min && 'Table'[Rate] <= 1 )
    )
VAR _b =
    CALCULATE (
        SUM ( 'Table'[Rate] ),
        FILTER ( 'Table', 'Table'[Rate] >= _Min && 'Table'[Rate] <= _Max )
    )
RETURN
    IF ( _Max = _Maxx, _a, _b )

Result is as below.

Default:

Use slicer:

You can download the pbix file from this link: how to use measure which is in percentage format in slicer

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Ashish_Mathur]

Hi,

Upload the file to Google Drive or similar service and share the download link.

[Greg_Deckler]

@Anonymous - I believe you essentially need a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

So, create a What If Parameter with your %'s, then something like:

Selector Measure = 
  VAR __Min = MIN('Slicer'[Value])
  VAR __Max = MAX('Slicer'[Value])
RETURN
  IF([OrdersRate]>=__Min && [OrdersRate]<=__Max,1,0)

Assumes OrdersRate is a measure, otherwise assuming you would use it in your slicer. You then filter the visual where Selector Measure = 1. If you filter all your visuals this way then they should all be affected by the slicer.

[Anonymous]

@Greg_Deckler  Hi Greg,

thanks for the response,

I'm having an error while creating this "selector Measure"

Error = Column 'Parameter Value' in table 'Parameter' cannot be found or may not be used in this expression

I can i resolve this?

Selector Measure =

VAR __Min = MIN('Parameter'[Parameter Value])

VAR __Max = MAX('Parameter'[Parameter Value])

RETURN

IF([fillrate]>=__Min && [fillrate]<=__Max,1,0)

[Greg_Deckler]

@Anonymous I don't know. Is this a measure or a column formula. If it is a measure, do you have that column in that table?

[Anonymous]

@Greg_Deckler  The parameter[value] is a measure auto created when a parameter is created with Whatif parameter functionality.

[amitchandak]

@Anonymous , you can use measure from slicer. You can create a slicer using what if or generate series and use that as filter 

To filer a measure you need force a row context in formula

Sumx(values(Table[Category]) ,if([Measure] <= selectedvalue(Parameter[parameter]), [Measure] , blank()))

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

Or you can create bucket using binning or segmentation

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

@amitchandak Thanks Amit,

Would it be possible to explain me how can i have what if parameter with my data. I want this Rate slicer to filter all the visuals i have in one page in my powerbi file with multiple measures in each of them.