Forum Discussion

KarenFingerhut's avatar
KarenFingerhut
Icon for Responsive Resident rankResponsive Resident
1 year ago
Solved

Percentage Slicer (slider)

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 

 

 

 

 

Need Help
  • KarenFingerhut's avatar
    KarenFingerhut
    1 year ago

    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
     

10 Replies

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

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

    • KarenFingerhut's avatar
      KarenFingerhut
      Icon for Responsive Resident rankResponsive Resident
      1 year ago

      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

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      KarenFingerhut
      Icon for Responsive Resident rankResponsive Resident
      1 year ago

      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's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

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

  • KarenFingerhut's avatar
    KarenFingerhut
    Icon for Responsive Resident rankResponsive Resident
    1 year ago

    Hi there

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

  • KarenFingerhut's avatar
    KarenFingerhut
    Icon for Responsive Resident rankResponsive Resident
    1 year ago

     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
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      KarenFingerhut
      Icon for Responsive Resident rankResponsive Resident
      1 year ago

      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's avatar
    KarenFingerhut
    Icon for Responsive Resident rankResponsive Resident
    1 year ago

    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