Forum Discussion
Helper IIIHow 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:
| Versions | Versions_Date | Contr_Margin | Prod_Code | Fact_Date |
| Actual | 01/01/1900 | 531 | 123 | 03/01/2023 |
| Actual | 01/01/1900 | 109 | 123 | 04/01/2023 |
| Actual | 01/01/1900 | 61 | 123 | 05/01/2023 |
| Actual | 225 | 123 | 06/01/2023 | |
| Actual | 260 | 123 | 07/01/2023 | |
| Actual | 260 | 123 | 08/01/2023 | |
| Actual | 158 | 123 | 09/01/2023 | |
| Actual | 204 | 123 | 10/01/2023 | |
| Actual | 647 | 123 | 11/01/2023 | |
| Actual | 102 | 123 | 12/01/2023 | |
| Actual | 240 | 123 | 01/01/2024 | |
| Actual | 306 | 123 | 02/01/2024 | |
| Actual | 340 | 123 | 03/01/2024 | |
| Actual | 266 | 123 | 04/01/2024 | |
| Actual | 01/01/1900 | 245 | 123 | 05/01/2024 |
| Actual | 01/01/1900 | 306 | 123 | 06/01/2024 |
| Actual | 01/01/1900 | 340 | 123 | 07/01/2024 |
| Fcst May | 05/01/2024 | 99 | 123 | 06/01/2024 |
| Fcst May | 05/01/2024 | 204 | 123 | 07/01/2024 |
| Fcst May | 05/01/2024 | 306 | 123 | 08/01/2024 |
| Fcst May | 05/01/2024 | 306 | 123 | 09/01/2024 |
| Fcst May | … | 531 | 123 | 10/01/2024 |
| Fcst May | 61 | 123 | 11/01/2024 | |
| Fcst May | 225 | 123 | 12/01/2024 | |
| Fcst May | 204 | 123 | 01/01/2025 | |
| Fcst May | 145 | 123 | 02/01/2025 | |
| Fcst May | 138 | 123 | 03/01/2025 | |
| Fcst May | 526 | 123 | 04/01/2025 | |
| Fcst May | 222 | 123 | 05/01/2025 | |
| Fcst May | 666 | 123 | 06/01/2025 | |
| Fcst May | 466 | 123 | 07/01/2025 | |
| Fcst May | 357 | 123 | 08/01/2025 | |
| Fcst May | 268 | 123 | 09/01/2025 | |
| Fcst May | 05/01/2024 | 486 | 123 | 10/01/2025 |
| Fcst May | 05/01/2024 | 548 | 123 | 11/01/2025 |
| Fcst May | 05/01/2024 | 266 | 123 | 12/01/2025 |
| Fcst June | 06/01/2024 | |||
| Fcst June | 06/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!
have to separate the measures. That will not work if you combine two measures.
5 Replies
ryan_mayuSuper User
you are welcome
- nicoenz
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?
