cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdityaPowerBI
Helper II
Helper II

Normalising measure in a visual with external(page level, visual level ) and internal filters

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:

ProductSales
A100
B200
C500
D300
E150
F250


Output:

ProductSalesMaxNormalized Sales
A1005000.2
B2005000.4
C5005001
D3005000.6
E1505000.3
F2505000.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.

 

7 REPLIES 7
Jihwan_Kim
Community Champion
Community Champion

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 

 

Picture17.png

 

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/

@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

Tahreem24
Super User II
Super User II

@AdityaPowerBI , First create measure to get the max sales value.

Max_Sales = CALCULATE(MAX(MyTable[Sales]),ALL(MyTable))
 
Then create second measure to get the final result:
Final Result = DIVIDE(SUM(MyTable[Sales]),[Max_Sales])
 
Screen shot attached for your reference:
Capture.PNG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

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.
Incorrect max value.PNG

amitchandak
Super User IV
Super User IV

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



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

Proud to be a Super User!

@amitchandak  Thanks for your reply. Sadly the measure didnt work. here is the output based on your measure.
Normal Sales.PNG

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors