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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vbvbvb
Employee
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.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
vbvbvb
Employee
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.


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.


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.


Go to My LinkedIn Page


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.