Forum Discussion

RobbeVL's avatar
RobbeVL
Impactful Individual
9 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 co...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 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
9 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
    9 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

    • RobbeVL's avatar
      RobbeVL
      Impactful Individual
      9 years ago
      Evolution/Week = 
VAR currentWeek =
    MAX ( 'DimDate'[Weeknumber] )
VAR currentBrand =
    FIRSTNONBLANK ( 'General_Deliveries'[BRAND] ; 2)
RETURN
    DIVIDE (
        [#Parcels]
            - CALCULATE (
                [#Parcels] ;
                FILTER (
                    ALL ( 'DimDate' );
                    'Dimdate'[Weeknumber]
                        = currentWeek - 1
                        && 'General_Deliveries'[BRAND] = currentBrand
                )
            );
        CALCULATE (
            [#Parcels];
            FILTER (
                ALL ( 'Dimdate' );
                'Dimdate'[Weeknumber]
                    = currentWeek - 1
                    && 'General_Deliveries'[BRAND] = currentBrand
            )
        )
    )

      v-ljerr-msft
      Using code above, I still get an error 
      "A single number for column BRAND cannot be determined.."

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

        Hi RobbeVL,

         

        The formula seems to be fine.

         

        Could you share a sample pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:

         

        Regards