% slider to show % range

[mb0307]

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:

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

 

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: 

Thanks you in advance. 

[v-xiaotang]

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:

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.

[mb0307]

@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::

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 · Accepted solution]

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.

[mb0307]

Worked.  Thank you so much for your response. 👍

[amitchandak]

@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