Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Divide column values by the maximum value of the same column

Here's my problem:

 

I have a column with the following values

 

 $                       325,00
 $                 17.086,86
 $                       240,00
 $                       216,00
 $                         16,00
 $                   1.900,00
 $                       960,00
 $                       161,00
 $                       200,00
 $                       790,00
 $                   1.935,98
 $                   1.211,00
 $                       268,00
 $                   4.293,89
 $                       128,45
 $                       288,00
 $                   4.918,92
 $                   3.000,00
 $                       500,00
 $                         50,00
 $                         50,00
 $                       900,00
 $                   5.100,00
 $                 10.800,00
 $                   1.700,00
 $                       588,63
 $                   7.400,00

 

I need to find out what the relative position of each value is based on the highest value in column (17.086,86).

 

To get the maximum value, I'm using:

 

Max = MAX(Plan1[Net Sales])

 

And to get the relative position, I created a calculated column:

RelativePosition = DIVIDE(Plan1[Net Sales],'My Measures'[Max],0)

But the formula returned only one column filled with 1.

How can I solve this?

Is there a better way to do this without creating the calculated column?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous ,

In Power Query you can add  custom column with the following code:

[Value] / List.Max( #"Sorted Rows"[Value])

That assumes that the values you want to compare exists in a column called value, and that the last Applied Step was called Sorted Rows.

 

You could also do this in DAX with the following measure:

Measure = 
var __Max = CALCULATE( MAX( Table2[Value]), ALL( Table2))
VAR __CurrentValue = MAX( Table2[Value])
RETURN


DIVIDE(  __CurrentValue,__Max)

 

The Var __Max is what would need some further refinement since it will give you the max of the entire table, which may or may not be what you are after, but that should get you started.

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@Anonymous ,

In Power Query you can add  custom column with the following code:

[Value] / List.Max( #"Sorted Rows"[Value])

That assumes that the values you want to compare exists in a column called value, and that the last Applied Step was called Sorted Rows.

 

You could also do this in DAX with the following measure:

Measure = 
var __Max = CALCULATE( MAX( Table2[Value]), ALL( Table2))
VAR __CurrentValue = MAX( Table2[Value])
RETURN


DIVIDE(  __CurrentValue,__Max)

 

The Var __Max is what would need some further refinement since it will give you the max of the entire table, which may or may not be what you are after, but that should get you started.

 

You could also do this in DAX with the following measure:

Measure = 
var __Max = CALCULATE( MAX( Table2[Value]), ALL( Table2))
VAR __CurrentValue = MAX( Table2[Value])
RETURN


DIVIDE(  __CurrentValue,__Max)

How should i take average of max in this formula? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.