Forum Discussion

guilherme_gq's avatar
guilherme_gq
Helper I
8 years ago
Solved

Cumulative value Based in other Measure

Hi!

I trying to calculate a Average Headcount (basically is the sum of Monthly HeadCount / number of months). The Monthly HeadCount is the measure that calculates a total value for the month.

 

for example:

 

                                      SET   OUT  NOV  DEZ    JAN
Monthly HeadCount    400   430   450    445    480
Average Headcount     400   415   427    431    441

 

 

  • October is (430 + 400)/2.
  • For November, is (450+430+400)/3.

 

 

does anyone have any idea how to calculate this measure?

 

Thanks!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi guilherme_gq,

     

    I would suggest you create a date table if you don't have one. Please check out the demo in the attachment.

    Measure =
CALCULATE (
    AVERAGEX (
        SUMMARIZE ( 'Table', 'Calendar'[MonthNum], "v", [Monthly HeadCount] ),
        [v]
    ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Date] <= MAX ( 'Table'[Date] )
    )
)

    Cumulative_value_Based_in_other_Measure

     

    Best Regards,

    Dale

3 Replies

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

    Hi guilherme_gq,

     

    I would suggest you create a date table if you don't have one. Please check out the demo in the attachment.

    Measure =
CALCULATE (
    AVERAGEX (
        SUMMARIZE ( 'Table', 'Calendar'[MonthNum], "v", [Monthly HeadCount] ),
        [v]
    ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Date] <= MAX ( 'Table'[Date] )
    )
)

    Cumulative_value_Based_in_other_Measure

     

    Best Regards,

    Dale