Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Thomas94401
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.

 

Thomas94401_0-1635199669013.png

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

vzhangti_1-1635414756076.png

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.

 

 

View solution in original post

7 REPLIES 7
TheoC
Super User
Super User

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

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!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

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

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!

Want to connect?www.linkedin.com/in/theoconias

v-zhangti
Community Support
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] ) )
)

vzhangti_1-1635385786669.png

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.

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

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

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!

Want to connect?www.linkedin.com/in/theoconias

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

vzhangti_1-1635414756076.png

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
Super User
Super User

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

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!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.