Percentage Slicer (slider)

[KarenFingerhut]

Hi there Community 

I hope someone can help me please

I have a granular level dataset going down to component level. To get the percentages of total adjusted I do the below calc

Firstly create 2 individual measures

Sum(components)

Sum(properties)

And the in another measure I divide one by the other to get %

Manchester  34%

Scotland 22%

Ireland 15%

Wales 3%

USA 55%

overall total = 33%

Obviously there's a lot more data in my query the above is just an example

The issue I have is I cannot add my calculated measure to a slicer (Slider) . Is there a way round of doing this please. I have tried creating parameters and doing generate series based on the above calculation but the issue is it's taking the overall total calculate as the max of the series 33%

Hope that makes sesnse

Fingers crossed someone can help please

Thank you

Karen 

[KarenFingerhut]

HI Gao thanks for getting back to me and apologies for my delayed reply. Sadly it's not working. Back to the drawing board 🙂 Kind regards Karen

[v-cgao-msft]

Hi @KarenFingerhut ,

What about dividing by 100 like this and comparing it to the original measure?

newMeasure = 
VAR __min_value = DIVIDE(MIN('Slider'[Value]),100)
VAR __max_value = DIVIDE(MAX('Slider'[Value]),100)
VAR __metric = [Measure]
VAR __result = IF(__metric>=__min_value && __metric<=__max_value,__metric)
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[KarenFingerhut · Accepted solution]

Hi Gao

I've now resolved the issue. 

I wrote some SQL to get a percentage distinct datset and created the numbers from 0.01 to 100.

I then created a % adjusted measure in my main dataset components[% Adjusted] by using these columns CALCULATE([# Total Adjusted]/[# Total Components] )

I then created a measure (see below) based on some of the responses to this thread (thank you everyone)

% Adjusted For Slider Link =

VAR sliderstart = MIN(percentagelist[% Adjusted]) - this is from my SQL dataset

VAR sliderend = MAX(percentagelist[% Adjusted])  - this is from my SQL dataset

VAR result = components[% Adjusted]  - this is from my main dataset

RETURN

IF( result >= sliderstart && result <= sliderend, result+0)

I then added percentagelist[% Adjusted] to each of my tables as well as to the slider . 

Works a treat.

Hope makes sense

Thank you everyone. Great team work 😊

Kind regards

Karen

[KarenFingerhut]

 also when creating the measure below and putting it in the table it is repeating the same value on every row

# Total Adjusted x # Total Components =

Var sliderstart = min(Slider[Value])

Var sliderend = max(Slider[Value])

Var result =

Divide([# Total Adjusted],[# Total Components])

Return

If( result >= sliderstart && result <= sliderend, result)
Thanks Karen

[KarenFingerhut]

Hi there

Sadly it doesn't work. Doing what you're suggesting but the create of the calculatetable isn't working with that measure.

[Deku]

You use the slider measure to create a calculated table and pull the column into the slicer

[Deku]

Add a table used to power the slider

Slider = generateseries(0,100,1)

Measure=

Var sliderstart = min(slider[value])

Var sliderend = max(slider[value])

Var result =

Divide(Sum(components), Sum(properties))

Return

If( result >= sliderstart && result <= sliderend, result)

[KarenFingerhut]

HI @Deku 

Thanks again for your reply. Unfortunately that doesn't work. It's not allowing me to add the above measure to a slicer

Thank you 

Kind regards

Karen

[Deku]

What would the slider do? If it was set at 50% would you only see USA?

[KarenFingerhut]

Hi @Deku 

Thanks for replying.

So the slider would allow me to choose a percentage range. So if I set it to show between 50% and 60% then yes it would only return USA

Many thanks

Karen