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 volume). Tha calculation is the following:
AVG volume * number of day in month/ number of day in month.
e.g:
january: 1000*31/31
february: ((1000*31)+(1051*28))/59
...
december: ((1000*31)+(1051*28)+....+(1048*31))/365
This is not problem, we can solve it in pl/sql.
But the sales team would like to make filtering for example : May and June, where they would like to see
AVG of May and June: ((1058*31)+(1064*30))/61
So this filtering caused for me some difficulty.
We have to also calculating margin for these periods, where the methodology is the same, and when we have margin, the we are able to calculate margin%--> margin/ avg volume
Do you have some tips for solving this issue?
Thank you in advance.
bálint
AvgCumulativeVolume=DIVIDE(SUMX('Sample','Sample'[Number of days in month]*'Sample'[Volume AVG]),SUM('Sample'[Number of days in month]))
7 Replies
daXtremeSolution Sage
Sample data/file needed.
- daXtreme
No, only Super Users can upload. But you can paste a link to a file on a shared drive (OneDrive, Google Drive and so on).
- wdx223_Daniel
Community Champion
AvgCumulativeVolume=DIVIDE(SUMX('Sample','Sample'[Number of days in month]*'Sample'[Volume AVG]),SUM('Sample'[Number of days in month]))
Hi wdx223_Daniel,
I tried the above mentioned solution in the original datamodel, but it is not works there.
In the dataset is more then 7 mn rows.
There is a volume column, where we can find eop and avg figures also, and there is a flag, where we can choose the eop and avg.
If i make a matrix, and i drop there the volume avg figures, and the number of days, and i make the multiplication and the division, then i get the correct figures.
But the above mentioned DAX provides back incorrect figures.
Have you any idea for this issue?
Thank you!