topic Re: Help with creating column which calulcates averrage values based on month + year value in Desktop

Help with creating column which calulcates averrage values based on month + year value

vbvbvb — Tue, 15 Nov 2022 14:33:21 GMT

Hello community,

Trying to see if there's a n easy way to implement column with DAX calculating following:

There is a table with a item, 50-60 per month, I want to calculate average values for them per month per each item conditionally based on their Month and Year , so as result, column would be populated with Average values per respective dates, taking to consideration Month + Year of items:

ID	Values	Month	Year	Average value ??
12345	10	October	2022	Sum of all values in October 2022 / count of October 2022 = 10 +5 /2 = 7.5
23456	20	November	2022	Sum of all values in Novemeber 2022/ count of November 2022 = 20/1 = 20
34567	15	September	2022	Sum of all values in September 2022/ count of September 2022
45678	5	October	2022	Sum of all values in October 2022 / count of October 2022
567890	10	August	2022	Sum of all values in August 2022/ count of August 2022
678901	20	October	2023	Sum of all values in October 2023/ count of October 2023
789012	10	August	2023	Sum of all values in August 2023/ count of August 2023
890123	5	November	2023	Sum of all values in Novemeber 2023/ count of Novemeber 2023

I suppose this can be achives by combining COUNTROWS + AVERAGE + PARALELLPERIOD, but can't wrap my head around actualy DAX to make it work correctly -- would appreaciate your guidance!

Re: Help with creating column which calulcates averrage values based on month + year value

Jihwan_Kim — Tue, 15 Nov 2022 15:25:42 GMT

Hi,

Please check the below picture and the attached pbix file.

AVG value CC = VAR _newtable = FILTER ( Data, Data[Year] = EARLIER ( Data[Year] ) && Data[Month] = EARLIER ( Data[Month] ) ) RETURN SUMX ( _newtable, Data[Values] ) / COUNTROWS ( _newtable )

Re: Help with creating column which calulcates averrage values based on month + year value

vbvbvb — Tue, 15 Nov 2022 15:34:53 GMT

Thanks , what is expected format of Data[Year] and Data[Month] ? I get this error when using thse fields:

Currently these values are calculcated as Year= YEAR(Data[Date]).

Re: Help with creating column which calulcates averrage values based on month + year value

Jihwan_Kim — Tue, 15 Nov 2022 15:37:39 GMT

Hi,

The suggest DAX formula is for creating a new column.

Could you please share your sample pbix file's link here? And then I can try to look into your sample pbix file to come up with a more accurate solution.

Thank you.

Re: Help with creating column which calulcates averrage values based on month + year value

vbvbvb — Tue, 15 Nov 2022 16:29:26 GMT

As suspected, this was an issue with format of these two columns -- I did new extraction of year + month and your DAX worked, thanks!

If I want to add extra argument to FILTER to also only consider another column for this calculation, for example if column = "Test" text value, can I add it directly to this formula?

Re: Help with creating column which calulcates averrage values based on month + year value

Jihwan_Kim — Tue, 15 Nov 2022 17:56:14 GMT

Hi,

Thank you for your reply, and I think it will work. If not, please share your sample pbix file and then I can try to look into it.

Thanks.

Re: Help with creating column which calulcates averrage values based on month + year value

vbvbvb — Tue, 15 Nov 2022 18:04:48 GMT

It did, i checked and seems that your solution was spot on , thansk once again!