Forum Discussion

RobbeVL's avatar
RobbeVL
Impactful Individual
8 years ago
Solved

Creating Evolution %

Hi all,

 

I created a matrix with some data of the amount of deliveries a partner receives per week.

I'd like to add the evolution per week to the matrix, how can I do this? (visually i'd add a column to Values, but how can I code this?)

 

Below you'll see what I mean visually, under that the current matrix

 

 

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi RobbeVL,

     

    Based on my test, the formula below should work in your scenario. 

    Evolution/Week =
VAR currentYear =
    MAX ( DimDate[CalendarYear] )
VAR currentWeek =
    MAX ( 'DimDate'[Weeknumber] )
RETURN
    DIVIDE (
        [#Parcels]
            - CALCULATE (
                [#Parcels],
                FILTER (
                    ALLEXCEPT ( General_Deliveries, General_Deliveries[BRAND] ),
                    RELATED ( 'DimDate'[CalendarYear] ) = currentYear
                        && RELATED ( 'Dimdate'[Weeknumber] )
                            = currentWeek - 1
                )
            ),
        CALCULATE (
            [#Parcels],
            FILTER (
                ALLEXCEPT ( General_Deliveries, General_Deliveries[BRAND] ),
                RELATED ( 'DimDate'[CalendarYear] ) = currentYear
                    && RELATED ( 'Dimdate'[Weeknumber] )
                        = currentWeek - 1
            )
        )
    )

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

9 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi RobbeVL,

     

    According to your description above, you should be able to use the formula below to create a new measure to calculate the EVO, then show it on the Matrix in your scenario. :smileyhappy:

    measure =
VAR currentWeek =
    MAX ( 'Table1'[Weeknumber] )
VAR currentBrand =
    FIRSTNONBLANK ( 'Table1'[BRAND], 1 )
RETURN
    DIVIDE (
        SUM ( 'Table1'[#Parcels] )
            - CALCULATE (
                SUM ( 'Table1'[#Parcels] ),
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Weeknumber]
                        = currentWeek - 1
                        && 'Table1'[BRAND] = currentBrand
                )
            ),
        CALCULATE (
            SUM ( 'Table1'[#Parcels] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Weeknumber]
                    = currentWeek - 1
                    && 'Table1'[BRAND] = currentBrand
            )
        )
    )

    Note: replace 'Table1' with your real table name.

     

    Regards

    • RobbeVL's avatar
      RobbeVL
      Impactful Individual
      8 years ago

      Thank you very much for your reply v-ljerr-msft .

      "# Parcels" is actually already a measure, how can I adjust the formula to that ?

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi RobbeVL,

         

        So could you try the formula below to see if it works? :smileyhappy:

        measure =
VAR currentWeek =
    MAX ( 'Table1'[Weeknumber] )
VAR currentBrand =
    FIRSTNONBLANK ( 'Table1'[BRAND], 1 )
RETURN
    DIVIDE (
        [#Parcels]
            - CALCULATE (
                [#Parcels] ,
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Weeknumber]
                        = currentWeek - 1
                        && 'Table1'[BRAND] = currentBrand
                )
            ),
        CALCULATE (
            [#Parcels],
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Weeknumber]
                    = currentWeek - 1
                    && 'Table1'[BRAND] = currentBrand
            )
        )
    )

         

        Regards