Last week in the table calculation

[danielcontente]

Hi, i have the following table, and would like to create a measure in which i will divide each item in the column "#Total Accumulative Amount" by the value in the column "Max Expected Amount" (26,834,488)

The desired result should be a column with the following numbers:

Thank you so much in advance.

[danielcontente]

working perfectly!!

appreciated it @tamerj1 

[tamerj1]

Hi @danielcontente 
Please try

=
DIVIDE (
    [#Total Accumulative Amount],
    CALCULATE ( [#Total Accumulative Amount], ALL ( 'Date'[Year Week number] ) )
)

[danielcontente]

Thanks so much @tamerj1 

however, i use a slicer for week, so if i filter for week 52, for example, there would be a new #max expected amount in week 52 and not 53. In this case, how can i do in order to consider the max value in the table depending on the filter?

[danielcontente]

example:

If i filter for week 52, then the table below would show only until week 52, and therefore all the values in the column "#Total Accumulative Amount" should be divided by the #Max Amount of week 52..

[tamerj1]

@danielcontente 

Try

=
DIVIDE (
[#Total Accumulative Amount],
CALCULATE (
[#Total Accumulative Amount],
ALLSELECTED ( 'Date'[Year Week number] )
)
)

[danielcontente]

Hi @tamerj1 

it didnt work.

It keeps dividing by the max amount not the amount of the max week.

Example:

because the amount in week 52 > week 53, even if a select week 53, it will divide by the amount of week 52.

[tamerj1 · Accepted solution]

@danielcontente 

Please try

=
DIVIDE (
[#Total Accumulative Amount],
MAXX (
TOPN (
1,
ADDCOLUMNS (
ALLSELECTED ( 'Date'[Year Week number] ),
"@MaxAmount", [#Max Amount]
),
'Date'[Year Week number]
),
[@MaxAmount]
)
)