Forum Discussion

vbvbvb's avatar
vbvbvb
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

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 f...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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 )
vbvbvb's avatar
vbvbvb
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

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?

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

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.

  • vbvbvb's avatar
    vbvbvb
    Icon for Microsoft Employee rankMicrosoft Employee
    3 years ago

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