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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
nicoenz
Helper III
Helper III

How can i sum this data without the overlapping values

hi everyone,

I want to create a simple sum function that allows me to select the Version (with a slicer) and sums the forecasted and the actual "Contr_Margin values" excluding overlaps.

I have the following table where Versions (Actual, Forecast May, Forecast June, etc). Actual dates have been recoded with 01/01/1900 so they can be used in any month:

VersionsVersions_DateContr_MarginProd_CodeFact_Date
Actual01/01/1900                     53112303/01/2023
Actual01/01/190010912304/01/2023
Actual01/01/1900                       6112305/01/2023
Actual                      22512306/01/2023
Actual                      26012307/01/2023
Actual                      26012308/01/2023
Actual                      15812309/01/2023
Actual                      20412310/01/2023
Actual                      64712311/01/2023
Actual                      10212312/01/2023
Actual                      24012301/01/2024
Actual                      30612302/01/2024
Actual                      34012303/01/2024
Actual                      26612304/01/2024
Actual01/01/1900                     24512305/01/2024
Actual01/01/1900                     30612306/01/2024
Actual01/01/1900                     34012307/01/2024
Fcst   May05/01/2024                       9912306/01/2024
Fcst   May05/01/2024                     20412307/01/2024
Fcst   May05/01/2024                     30612308/01/2024
Fcst   May05/01/2024                     30612309/01/2024
Fcst   May                     53112310/01/2024
Fcst   May                        6112311/01/2024
Fcst   May                      22512312/01/2024
Fcst   May                      20412301/01/2025
Fcst   May                      14512302/01/2025
Fcst   May                      13812303/01/2025
Fcst   May                      52612304/01/2025
Fcst   May                      22212305/01/2025
Fcst   May                      66612306/01/2025
Fcst   May                      46612307/01/2025
Fcst   May                      35712308/01/2025
Fcst   May                      26812309/01/2025
Fcst   May05/01/2024                     48612310/01/2025
Fcst   May05/01/2024                     54812311/01/2025
Fcst   May05/01/2024                     26612312/01/2025
Fcst   June06/01/2024   
Fcst   June06/01/2024   
Fcst   June    

 

I tried many different ways of summing my data but always get the same result. Whatever i try its sums overlapping values (June,July in Forecast May version).

Below pivot table shows what I get and what I would like to get:

 

 Sum of Contr_Margin  Versions    
 Fact_Date  Actual  forecast May2024  my results  Expected result 
03/01/2023      531                              531                             531
04/01/2023     109                              109                             109
05/01/2023       61                                61                               61
06/01/2023        225                              225                             225
07/01/2023     260                              260                             260
08/01/2023   260                              260                             260
09/01/2023   158                              158                             158
10/01/2023  204         204                             204
11/01/2023    647                              647                             647
12/01/2023           102                              102                             102
01/01/2024         240                              240                             240
02/01/2024          306                              306                             306
03/01/2024       340                              340                             340
04/01/2024        266                              266                             266
05/01/2024          245                              245                             245
06/01/2024          306                               99                             405                             306
07/01/2024            340                             204                             544                             340
08/01/2024                              306                             306                             306
09/01/2024                              306                             306                             306
10/01/2024                              531                             531                             531
11/01/2024                                61                               61                               61
12/01/2024                              225                             225                             225
01/01/2025                              204                             204                             204
     
  Sum:                         6'536                         6'233

 

Any help will be greatly appreciated!

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@nicoenz 

pls see if this is what you want

11.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

have to separate the measures. That will not work if you combine two measures.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
nicoenz
Helper III
Helper III

thanks!!!!! @ryan_mayu 

 

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




it works perfectly well the way you have it.

i tried integrating everything into a single measure and i get the wrong column total. any idea why?
i defined:

Measure 2 =     

var actual2 = CALCULATE(sum('Table (2)'[Contr_Margin]),'Table (2)'[Versions]="Actual")

var fc = CALCULATE(sum('Table (2)'[Contr_Margin]),'Table (2)'[Versions]="Fcst May")

var measure = if(ISBLANK([actual2]),[fc],[actual2])

return

sumx(values('Table (2)'[Fact_Date]),[Measure])

Can all be integrated into a single measure?

have to separate the measures. That will not work if you combine two measures.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@nicoenz 

pls see if this is what you want

11.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.