Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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
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.
Worked. Thank you so much for your response. 👍
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |