Forum Discussion
Responsive Resident% 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:
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.
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.
5 Replies
- amitchandak
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
v-xiaotangCommunity 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:
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.
mb0307Exactly 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
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.
