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,
I am working on visuals where I need to normalize(all values in the range of 0-100) the values present in the visual. It means I cannot create a calculated column I need to create a measure which first calculate the maximum values out of the given values in the visual and then divide all the row values by this maximum values.
Input:
Product | Sales |
A | 100 |
B | 200 |
C | 500 |
D | 300 |
E | 150 |
F | 250 |
Output:
Product | Sales | Max | Normalized Sales |
A | 100 | 500 | 0.2 |
B | 200 | 500 | 0.4 |
C | 500 | 500 | 1 |
D | 300 | 500 | 0.6 |
E | 150 | 500 | 0.3 |
F | 250 | 500 | 0.5 |
There can be page level filter applied on this visual, so our measure needs to consider that. There could be other column than product and other measure than sales in other visuals. I want to create a repeatable solution. Appreciate if you could help. The data is not at the level of Product column there are other columns also in the table like category, subcategory. Only the visual is bringing the data per product.
Hi, @AdityaPowerBI
Please correct me if I understood wrongly.
Please check the sample pbix file's link down below and the measure.
Result =
DIVIDE (
SUM ( 'Normal'[Sales] ),
CALCULATE ( MAX ( 'Normal'[Sales] ), ALLSELECTED ( 'Normal'[Product] ) )
)
https://www.dropbox.com/s/yzvwh2a1ewp2s0k/adityav2.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim Thanks for the reply. It worked for me for the sample file. But the main data didnt respond positively to this measure. It is not calculating the max value correctly. The main data has 8-9 dimension columns and sales as the only measure.
This could be one of the reason. I added one more dimension category and updated my data:
Product Category Sales
A C1 100
B C1 200
C C2 500
D C2 300
E C3 150
F C4 250
Now if I bring Category and Sales to the table visual and try to find max value of the visual the measure still generates the max value of the product not category. Output looks like below:
max tables sales measure =
CALCULATE(MAX('Table'[Sales]),ALLSELECTED('Table'))
Category Max table Sales Sales
C1 500 300
C2 500 800
C3 500 150
C4 500 250
@AdityaPowerBI , First create measure to get the max sales value.
Thanks @Tahreem24 for your help. Your solution works for a static visual which bring same max value irrespective of any filter or slicer applied.. But in case I add a visual level or page level filter, it fails to calculate the max value correctly. here is the result of your measure.
@AdityaPowerBI , In case sales is a column
new column
divide([Sales] ,maxx(table, [sales]) )
if sales is a measure a new measure
divide([Sales] ,calculate(maxx(values(Table[product]), [sales]), allselected(Table) ))
@amitchandak Thanks for your reply. Sadly the measure didnt work. here is the output based on your measure.
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |