Forum Discussion
Cumulative sum
I have a dataset contains 52 week of sales :
the table below is a sample of the dataset I have.
Week | Sales | Actual/forecast |
1 | 10 | Actual |
1 | 12 | Forecast |
2 | 14 | Actual |
2 | 20 | Forecast |
3 | - | Actual |
3 | 6 | Forecast |
4 | - | Actual |
4 | 8 | Forecast |
I want to create a visual table in power bi that looks like the below table which basically does the following sum the total actual sales up to the selected week and if there is no actual for the selected week I want to replace it with forecast vale just like roll over column shows.
Week | Actual | Forecast | Cumulative sum |
1 | 10 | 12 | 10 |
2 | 14 | 20 | = 10+14 = 24 |
3 | - | 6 | =24+6=30 |
| _ | 8 | 30+8= 38 |
I wrote this formula but seems like it only gives me the right output up to week 3 and wrong values after week 3 why is that
test = VAR SelectedWeek = MAX('salesdata[week] VAR ActualSumUpToSelectedWeek = CALCULATE( SUM(salesdata'[Value]), salesdata[actual/forcast] = "actual", salesdata[week] <= SelectedWeek )RETURN ActualSumUpToSelectedWeek + if([ActualSum] = 0, [ForecastSum],[ActualSum]).
can someone help me please
Add this measure or club: I still not understand the intention ...
Sales for Actual = CALCULATE( SUM('salesdata'[Sales ]), 'salesdata'[Actual/forecast] IN { "Actual" } ) Sales for Forecast = CALCULATE( SUM('salesdata'[Sales ]), 'salesdata'[Actual/forecast] IN { "Forecast" } ) CT Actual or Forecast = SUMX ( CALCULATETABLE( values(salesdata[Week ]), salesdata[Week ] <= MAX(salesdata[Week ])) , COALESCE( salesdata[Sales for Actual], [Sales for Forecast]) )If you dont want multiple measures, you can club the measure logic into one measure!
Hoep this helps!
I do NOT see any issue.
Check these screenshots:
a) No selection
b) with selection of Week 2, week 2
b) with selection of Week 1, Week 4
8 Replies
Read about COALESCE
SUMX(<table>,COALESCE([Actual],[Forecast]))
Filter by week etc.
Add this measure or club: I still not understand the intention ...
Sales for Actual = CALCULATE( SUM('salesdata'[Sales ]), 'salesdata'[Actual/forecast] IN { "Actual" } ) Sales for Forecast = CALCULATE( SUM('salesdata'[Sales ]), 'salesdata'[Actual/forecast] IN { "Forecast" } ) CT Actual or Forecast = SUMX ( CALCULATETABLE( values(salesdata[Week ]), salesdata[Week ] <= MAX(salesdata[Week ])) , COALESCE( salesdata[Sales for Actual], [Sales for Forecast]) )If you dont want multiple measures, you can club the measure logic into one measure!
Hoep this helps!
It works thanks a lot for your help; quick one please why this formula does not work when selecting multiple weeks from a slicer; say I want to have a table that shows week number, actual, forecast, ct actual if forecasts and use week column in a slicer so I can choose whatever week I want and display the results I also want to have the option to choose mutiple week and still get the results I want following the logic I explained earlier. Thank you 🙂
I do NOT see any issue.
Check these screenshots:
a) No selection
b) with selection of Week 2, week 2
b) with selection of Week 1, Week 4
Hi I have a question please - I have created two variables minweek and max week and used your formula and added min week so when I select for example week 3 and 6 I want to sum the actuals from week 3 to week 6 and not from week 1 to week 6 but it does not work for some reason
U have to share the DAX or .pbix for us to understand. 🙂
if u r trying to filter the range of weeks as min and max , you may need to tweak this line as:
CALCULATETABLE( values(salesdata[Week ]), salesdata[Week ] >= MIN(salesdata[Week ] && salesdata[Week ] <= MAX(salesdata[Week ] )
Hi,
Do you have a Date column? If no, then do you have Year and Month columns? If you do have either, then share some data to work with. Also, share a Calendar table with a Week number column.
