topic Re: Cumulative and filtered calculation in DAX Commands and Tips

Cumulative and filtered calculation

Anonymous — Sun, 06 Nov 2022 19:40:09 GMT

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

Re: Cumulative and filtered calculation

daXtreme — Sun, 06 Nov 2022 19:51:08 GMT

Sample data/file needed.

Re: Cumulative and filtered calculation

Anonymous — Sun, 06 Nov 2022 21:10:22 GMT

Can I pbix file upload here?

I tried upload xls, txt, and powebi, but it doesnot work.

Thank you

Re: Cumulative and filtered calculation

daXtreme — Mon, 07 Nov 2022 00:43:21 GMT

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).

Re: Cumulative and filtered calculation

Anonymous — Mon, 07 Nov 2022 07:09:40 GMT

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!

Re: Cumulative and filtered calculation

wdx223_Daniel — Mon, 07 Nov 2022 07:23:47 GMT

AvgCumulativeVolume=DIVIDE(SUMX('Sample','Sample'[Number of days in month]*'Sample'[Volume AVG]),SUM('Sample'[Number of days in month]))

Re: Cumulative and filtered calculation

Anonymous — Mon, 07 Nov 2022 09:56:25 GMT

Thank you, its working fine.

Re: Cumulative and filtered calculation

Anonymous — Tue, 08 Nov 2022 12:35:27 GMT

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!