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

[Anonymous]

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.

[Jihwan_Kim]

Hi, @Anonymous 

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/

[Anonymous]

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

[Anonymous]

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]

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

[Anonymous]

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.

[amitchandak]

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

[Anonymous]

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