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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
shad0wca7
Frequent Visitor

Calculating sum of unique historical values

I have a number of deals in a table which are historical values - all changes to the deal stage are recorded but what I want to have is a sum of the historical amounts for a unique deal ID (e.g. not just the sum of all changes for a particular deal ID.. but the sum of all deals in a time period using the most relevant date and unique ID)

 

dealidStageDateDeal value
1Open01/04/2013 $         1.00
1Evaluating10/04/2013 $         2.00
1Negotiation12/04/2013 $         3.00
2Open10/04/2013 $         1.00
2Evaluating11/04/2013 $         2.00
2Closed15/04/2013 $         1.00
1Won16/04/2013 $         3.00

 

I want to display this per quarter e.g. I want to know what the sum is of all deals that I've been dealing with on a particular quarter and then be able to filter by stage. This is because the values of these deals change depending on their pipeline stages often (or as more is known about the deal)...

 

I'm struggling to find an elegant way to do this and making myself confused.. Any help much appreciated

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @shad0wca7 ,

 

Not quite understand what you want, are you want the sum of value per id and per quarter? If so please refer to the measure below.

Measure = CALCULATE(SUM('Table'[Deal value]),FILTER(ALLEXCEPT('Table','Table'[dealid]),FORMAT('Table'[Date],"q")=FORMAT(SELECTEDVALUE('Table'[Date]),"q")))

 1.PNG

If i misunderstood your meaning, please let me know.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

1 REPLY 1
v-jayw-msft
Community Support
Community Support

Hi @shad0wca7 ,

 

Not quite understand what you want, are you want the sum of value per id and per quarter? If so please refer to the measure below.

Measure = CALCULATE(SUM('Table'[Deal value]),FILTER(ALLEXCEPT('Table','Table'[dealid]),FORMAT('Table'[Date],"q")=FORMAT(SELECTEDVALUE('Table'[Date]),"q")))

 1.PNG

If i misunderstood your meaning, please let me know.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.