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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
nileshpca
Frequent Visitor

calculate average from table with date wise price changes

to keep things simple, my table has date and price

date of price change | price

01-Jan-21 | 22

04-Jan-21 | 20

16-Jan-21 | 18

22-Jan-21 | 21

 

average will calculate simple average of the four prices. i want dax formula to calculate average for the period considering the price for each day

 

date | price

01-Jan-21 | 22

02-Jan-21 | 22

03-Jan-21 | 22

04-Jan-21 | 20

04-Jan-21 | 20

...

 

tried experimenting with lastnonblank but no success. please help

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @nileshpca ,

Here are the steps you can follow:

1. Create measure.

table1_sum =
var _summtable=
SUMMARIZE('Table 2','Table 2'[all date],'Table 2'[price],
"price_change",
var _1=CALCULATE(SUM('Table 2'[price]),FILTER(ALL('Table 2'),'Table 2'[all date]=EARLIER('Table 2'[all date])))
var _2=CALCULATE(SUM('Table 2'[price]),FILTER(ALL('Table 2'),'Table 2'[all date]=EARLIER('Table 2'[all date])-1))
return
IF(
    _1<> _2,MINX(FILTER(ALL('Table 2'),'Table 2'[price]=EARLIER('Table 2'[price])),[price]),BLANK()),
"count",COUNTX(FILTER(ALL('Table 2'),'Table 2'[price]=EARLIER('Table 2'[price])),[all date]),
"count_all",COUNTX(ALL('Table 2'),[all date]),
"sum_all",SUMX(ALL('Table 2'),[price]))
return
DIVIDE( SUMX(_summtable,[sum_all]),SUMX(_summtable,[count_all]))

2. Result:

vyangliumsft_0-1647933852628.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

14 REPLIES 14
v-yangliu-msft
Community Support
Community Support

Hi  @nileshpca ,

Here are the steps you can follow:

1. Create measure.

table1_sum =
var _summtable=
SUMMARIZE('Table 2','Table 2'[all date],'Table 2'[price],
"price_change",
var _1=CALCULATE(SUM('Table 2'[price]),FILTER(ALL('Table 2'),'Table 2'[all date]=EARLIER('Table 2'[all date])))
var _2=CALCULATE(SUM('Table 2'[price]),FILTER(ALL('Table 2'),'Table 2'[all date]=EARLIER('Table 2'[all date])-1))
return
IF(
    _1<> _2,MINX(FILTER(ALL('Table 2'),'Table 2'[price]=EARLIER('Table 2'[price])),[price]),BLANK()),
"count",COUNTX(FILTER(ALL('Table 2'),'Table 2'[price]=EARLIER('Table 2'[price])),[all date]),
"count_all",COUNTX(ALL('Table 2'),[all date]),
"sum_all",SUMX(ALL('Table 2'),[price]))
return
DIVIDE( SUMX(_summtable,[sum_all]),SUMX(_summtable,[count_all]))

2. Result:

vyangliumsft_0-1647933852628.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

thanks.. this works perfect

nileshpca
Frequent Visitor

hi,

thanks...

it is giving simple average of the four prices. i want to consider the price for all the days of the month. if i am not clear, uploading sample file with solution required..

 

https://drive.google.com/file/d/15u4fZG4Y2IGHWnGv9G78R6i_OwTXuSXL/view?usp=sharing

 

nilesh

this works great.. but how do i use this if there are 50 products across 6 years.. will have to make price with all dates for 6 years for each product. Can there be a dax solution without calculated column... I tried but it seems EARLIER cannot be used in measure.

Yes, I see. You need to merge table 1 into Table 2 (on date) and choose Fill Down for the new column with only four entries with the transform tab. Then you can apply your regular average formula to this new column(Price1).

 

Whitewater100_0-1647623902688.png

 

hi,

thanks.. was looking for a dax measure instead of power query solution since there are lot of products involved. example was given for one product to keep it simple

 

 

Hi:

I have created another way to get to yout initial question.

 

Power Query(example#1) is the best for lot's of items and changes.

 

So far we have three different solutions to your original question. 

 

If you want to post a new question with new data.

 

Here is the file link with the various solutions.

 

Thanks!https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing 

thanks a lot. i am using powerquery currently, but only for 2-3 products. there are 50 + products over 6 years. the pq table will be huge drain on memory.

was thinking is it possible to create virtual table in dax with date column from calender and product selected from filter row and then fill down in the virtual table.

that would be awesome, if it can happen...

Hi:

I beleive if you have an additional date table (like your table 2)and run the PQ process I mentioned (and DAX sum measure after) it will be a faster solution pushing the procesing back to the query editor. Pushing back closer to source is generally best practice.

 

That said, here is new update to file. You just need to have your table with the four prices in it filled out with all the dates for the month. I called it "Price Table". There is DAX Calc Col.

 

I hope this is the solution to work for you!https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing 

This link has the DAX Measure in it.

New Avg Price = AVERAGE(PriceTable[New Price Fill Down])
 

 

https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing 

Hello:

 

This can give 27 as answer:

 

Avg Price II =
DIVIDE(SUM('Table 2'[Price]),
COUNTROWS('Table 2'))
 
Is this the measure you want? Thanks..

i want the answer 27 from Table1. Table2 is not in the actual database and is to show how the average is to be calculated.

amitchandak
Super User
Super User

@nileshpca ,

 

If price is column

averageX(allselected(Table), Table[price])


if price is a measure
calculate(averageX(values(Table[Date], [price]), allselected())

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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