Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vbvbvb
Microsoft Employee
Microsoft Employee

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

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:

 

IDValuesMonth YearAverage value ??
1234510October2022Sum of all values in October 2022 / count of October 2022 = 10 +5 /2 = 7.5
2345620November2022Sum of all values in Novemeber 2022/ count of November 2022 = 20/1 = 20
3456715September2022Sum of all values in September 2022/ count of September 2022
456785October2022Sum of all values in October 2022 / count of October 2022
56789010August2022Sum of all values in August 2022/ count of August 2022
67890120October2023Sum of all values in October 2023/ count of October 2023
78901210August2023Sum of all values in August 2023/ count of August 2023
8901235November2023Sum 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!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1668525915830.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
vbvbvb
Microsoft Employee
Microsoft Employee

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

vbvbvb_0-1668526421221.png

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

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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?

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1668525915830.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors