Forum Discussion

norbi's avatar
norbi
Icon for Helper I rankHelper I
8 years ago

Measures for Customer Trends

Hi, I would appreciate assistance in solving the percentage Month on Month (MoM) growth, percentage Year on Year (YoY) growth and percentage of 3 month average. Below is the table where the input is...
Need Help
norbi's avatar
norbi
Icon for Helper I rankHelper I
8 years ago

Hi Zubair_Muhammad,

 

Thank you for the feedback it works well, however how would you achieve the sum of the Customers if there are more than one in a month? E.g.

Date

Name

Vol

Jan-16

Cust_1

3

Jan-16

Cust_2

4

Jan-16

Cust_3

1

Jan-16

Cust_4

2

Feb-16

Cust_1

2

Feb-16

Cust_2

1

Feb-16

Cust_3

3

Feb-16

Cust_4

6

…..

 

 

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi norbi

 

Just Replace

 

ALLEXCEPT ( TableName, TableName[Name] )

 

With

 

ALL (TableName)
  • norbi's avatar
    norbi
    Icon for Helper I rankHelper I
    8 years ago

    Hi Zubair_Muhammad,

     

    How would the average work for the three month average as numerous Cust_* need to be added together and then averaged per month?

    % of 3 month_average =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        AVERAGE ( TableName[Vol] ),
        DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -3, MONTH )
    )
)

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      HI norbi

       

      See the Addition in Red font

       

      % of 3 month_average =
DIVIDE (
    SUM ( TableName[Vol] ),
    CALCULATE (
        AVERAGE ( TableName[Vol] ),
        ALL ( TableName ),
        DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -3, MONTH )
    )
)

       

       

      • norbi's avatar
        norbi
        Icon for Helper I rankHelper I
        8 years ago

        Hi Zubair_Muhammad

         

        Thank you or the feedback. When I try the formula with multiple entries for the month it averages every value and over three months. I would like to sum the entire month of data for all customer and the average only on the monthly totals for 3 months? Your assistance will be highly appreciated.