cancel
Showing results for
Did you mean: Frequent Visitor

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

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])
)
)

1 ACCEPTED SOLUTION  Community Support

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.

7 REPLIES 7  Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!  Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!  Community Support

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. Frequent Visitor

Hi v-zhangti,

Unfortunely, what I want in the column Cumulative_Amount_per_people is 😀

 Cumulative_Amount_per_people 1,02 3,09 5,97  Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!  Community Support

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.  Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!   