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.   fo...
  • 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

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

    Hi guilherme_gq,

     

    Could you please mark the proper answer as a solution?

     

    Best Regards,
    Dale

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

  • guilherme_gq's avatar
    guilherme_gq
    Helper I
    8 years ago

    Thanks, man!!!

     

    that worked!!!