How to compute a cumulative value for "amount per person" month by month ?

[Thomas94401]

Hi, 

I would like to compute a cumulative sum for "amount per person" month by month.

My measure for computing "Cumulative Amout Per person" is wrong because, for instance, for month 2, it is doing (114 + 267) / (112 + 129). Instead of 1,02 + 2,07

Do you know if it is possible to get this result ?

My formula are : 

Sales_P1 = SUM('Sales'[Conso])

Pop_P1 = SUM('Population'[Pop])

Amount_per_person = DIVIDE([Sales_P1 ],Population[POP_P1])

Cumulative_amount_per_person = 

CALCULATE(

[Amount_per_person ],

FILTER(

ALLSELECTED('Sales'[Date]),

'Sales'[Date] <= MAX('Sales'[Date])

),

FILTER(

ALLSELECTED('Sales'[Number_month]),

'Sales'[Number_month] <= MAX('Sales'[Number_month])

)

)

Thanks in advance !!

[TheoC]

Hi @Thomas94401 

I may be misunderstanding what you are requesting, however, the measures you have created are correct in what they are calculating.  I believe you need to modify your Sales_P1 and Pop_P1 measures to the following:

Sales_P1 = TOTALYTD ( SUM ( 'Sales'[Conso] ) , 'Date'[Date] )
Pop_P1 = TOTALYTD (SUM ( 'Population'[Pop] ) , 'Date'[Date] )

Give the above a go and hopefully that resolves it 🙂

Theo

[TheoC]

Hi @Thomas94401 

I may be misunderstanding what you are requesting, however, the measures you have created are correct in what they are calculating.  I believe you need to modify your Sales_P1 and Pop_P1 measures to the following:

Sales_P1 = TOTALYTD ( SUM ( 'Sales'[Conso] ) , 'Date'[Date] )
Pop_P1 = TOTALYTD (SUM ( 'Population'[Pop] ) , 'Date'[Date] )

Give the above a go and hopefully that resolves it 🙂

Theo

[v-zhangti]

Hi,@Thomas94401

 You can try the following methods.

Cumulative_Amount_per_person =
CALCULATE (
    SUM ( 'Table'[Sales_P1] ),
    FILTER ( 'Table', 'Table'[Month] <= EARLIER ( 'Table'[Month] ) )
)
    / CALCULATE (
        SUM ( 'Table'[Amount_per_person] ),
        FILTER ( 'Table', 'Table'[Month] <= EARLIER ( 'Table'[Month] ) )
)

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlottez 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thomas94401]

Hi v-zhangti,

Thanks for your reply ! 

Unfortunely, what I want in the column Cumulative_Amount_per_people is 😀

Cumulative_Amount_per_people

1,02

3,09

5,97

[TheoC]

Hi @Thomas94401 

I may be misunderstanding what you are requesting, however, the measures you have created are correct in what they are calculating.  I believe you need to modify your Sales_P1 and Pop_P1 measures to the following:

Sales_P1 = TOTALYTD ( SUM ( 'Sales'[Conso] ) , 'Date'[Date] )
Pop_P1 = TOTALYTD (SUM ( 'Population'[Pop] ) , 'Date'[Date] )

Give the above a go and hopefully that resolves it 🙂

Theo

[v-zhangti · Accepted solution]

Hi,@Thomas94401

Please confirm whether this output is correct.

Cumulative_Amount_per_person =
CALCULATE (
    SUM ( 'Table'[Amount_per_person] ),
    FILTER ( 'Table', 'Table'[Month] <= EARLIER ( 'Table'[Month] ) )
)

Best Regards,

Community Support Team _Charlottez 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheoC]

Hi @Thomas94401 

I may be misunderstanding what you are requesting, however, the measures you have created are correct in what they are calculating.  I believe you need to modify your Sales_P1 and Pop_P1 measures to the following:

Sales_P1 = TOTALYTD ( SUM ( 'Sales'[Conso] ) , 'Date'[Date] )
Pop_P1 = TOTALYTD (SUM ( 'Population'[Pop] ) , 'Date'[Date] )

Give the above a go and hopefully that resolves it 🙂

Theo