Forum Discussion
Cumulative and filtered calculation
Hi Colleagues, I need again some help 🙂 In the DB we have AVG volume, and we use for visualisaton a line and clustered chart. Tha sales team need another avg (we call it cumulative avg volu...
AvgCumulativeVolume=DIVIDE(SUMX('Sample','Sample'[Number of days in month]*'Sample'[Volume AVG]),SUM('Sample'[Number of days in month]))
daXtreme
Solution Sage
Solution SageNo, only Super Users can upload. But you can paste a link to a file on a shared drive (OneDrive, Google Drive and so on).
Ok, then i make a short table with figures:
| Yearmonth | Customer | Number of days in month | number of days | Volume AVG | AVG volume weighted | AVG volume weighted cumulative | avg cumulative volume |
| 202201 | XY | 31 | 31 | 100000 | 3100000 | 3100000 | 100000 |
| 202202 | xy | 28 | 59 | 100030 | 2800840 | 5900840 | 100014 |
| 202203 | xy | 31 | 90 | 99874 | 3096094 | 8996934 | 99966 |
| 202204 | xy | 30 | 120 | 96784 | 2903520 | 11900454 | 99170 |
| 202205 | xy | 31 | 151 | 101234 | 3138254 | 15038708 | 99594 |
| 202206 | xy | 30 | 181 | 103496 | 3104880 | 18143588 | 100241 |
| 202207 | xy | 31 | 212 | 145670 | 4515770 | 22659358 | 106884 |
So i would like to make a visualisation, where without filtering we can see the avg cumulated avg volume (in the example 202207), but when we filtering for example 202203 and 202204, then (202203 avg volume*31 + 202204avg volume * 30)/61.
Thank you in advance!