Forum Discussion

danielcontente's avatar
danielcontente
Icon for Helper II rankHelper II
3 years ago
Solved

Last week in the table calculation

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.

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    danielcontente 

    Please try

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

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi danielcontente 
    Please try

    =
DIVIDE (
    [#Total Accumulative Amount],
    CALCULATE ( [#Total Accumulative Amount], ALL ( 'Date'[Year Week number] ) )
)
    • danielcontente's avatar
      danielcontente
      Icon for Helper II rankHelper II
      3 years ago

      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?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        danielcontente 

        Try

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