cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mb0307
Post Patron
Post Patron

% slider to show % range

Hi all,

 

In my Sales Table,  Sales% is calculated at row level agaisnt the Target column.

 

First visual Table (not Matrix),  shows Product, Sales, Target and % like this:

1.jpg

 

Requirement 1:  I want to show summary of Product and % Range excluding any 0% values, like this:

2.jpg 

 

Requirement 2:  I want to add % Slider for users to slice the data for any % range.   But this should also update the Matrix table % Range.

 

Example:  % Slider set to 50 - 80%

 

Result: 

3.jpg

4.jpg

 

Thanks you in advance. 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @mb0307 

Not very clear about the result you want. 

You said you have a PRODUCT master dim table with one-2-many relationship to Sales and Other tables. Do you want to filter through the product column in the PRODUCT table ? 

If so, try this.

Measure_edit = 
var _product=SELECTEDVALUE('Product'[Product])
var _filtertable=FILTER(ALL('Sales Table'),'Sales Table'[Product]=_product&&[Column_%]>0)
VAR _minrange=CALCULATE(MIN([Column_%]),_filtertable)
var _maxrange=CALCULATE(MAX([Column_%]),_filtertable)
return FORMAT(_minrange,"Percent")&"-"&FORMAT(_maxrange,"Percent")

Best Regards,

Community Support Team _ Tang

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

View solution in original post

5 REPLIES 5
v-xiaotang
Community Support
Community Support

Hi @mb0307 

Try this.

-

Create a column:

Column_% = DIVIDE('Sales Table'[Sales],'Sales Table'[Target ])

Then, create a measure:

Measure_%_Range =
VAR _filtertable =
    FILTER ( ALLEXCEPT ( 'Sales Table', 'Sales Table'[Product] ), [Column_%] > 0 )
VAR _minrange =
    CALCULATE ( MIN ( [Column_%] ), _filtertable )
VAR _maxrange =
    CALCULATE ( MAX ( [Column_%] ), _filtertable )
RETURN
    FORMAT ( _minrange, "Percent" ) & "-"
        & FORMAT ( _maxrange, "Percent" )

Result:

v-xiaotang_0-1620884126875.png

Please check the sample file I attached below.

 

Best Regards,

Community Support Team _ Tang

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

@v-xiaotang 

 

Exactly what I need but one question which I forgot to add in the post:

 

I have a PRODUCT master dim table with one-2-many relationship to Sales and Other tables, like this::

Screenshot 2021-05-13 164052.jpg

 

Can the measure be tweaked for PRODUCT dim table and all other columns (sales, target and %) are in Sales table.  Highlighted in red below:

'Product'[Product]

 

 

 

Measure_%_Range =
VAR _filtertable =
FILTER ( ALLEXCEPT ( 'Sales Table', 'Sales Table'[Product] ), [Column_%] > 0 )
VAR _minrange =
CALCULATE ( MIN ( [Column_%] ), _filtertable )
VAR _maxrange =
CALCULATE ( MAX ( [Column_%] ), _filtertable )
RETURN
FORMAT ( _minrange, "Percent" ) & "-"
& FORMAT ( _maxrange, "Percent" )

 

Thanks

v-xiaotang
Community Support
Community Support

Hi @mb0307 

Not very clear about the result you want. 

You said you have a PRODUCT master dim table with one-2-many relationship to Sales and Other tables. Do you want to filter through the product column in the PRODUCT table ? 

If so, try this.

Measure_edit = 
var _product=SELECTEDVALUE('Product'[Product])
var _filtertable=FILTER(ALL('Sales Table'),'Sales Table'[Product]=_product&&[Column_%]>0)
VAR _minrange=CALCULATE(MIN([Column_%]),_filtertable)
var _maxrange=CALCULATE(MAX([Column_%]),_filtertable)
return FORMAT(_minrange,"Percent")&"-"&FORMAT(_maxrange,"Percent")

Best Regards,

Community Support Team _ Tang

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

View solution in original post

Worked.  Thank you so much for your response. 👍

amitchandak
Super User
Super User

@mb0307 , I doubt this can approched like this. You can have similat buckets like 50-80, not like one is 50-80 and another is 50 -75

 

If you want that then you need have one more column

 

new column =
var _min = minx(filter(Table, [Product] =earlier([product])),[%])
var _max = maxx(filter(Table, [Product] =earlier([product])),[%])
return // * 100 is optional
if(_min = _max, _max *100 & "" , _min & " " & _max)

 

better approch measure bucket

refer my video

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.