Forum Discussion
Running Division on First Date
Anonymous you can see @edhans answer for this same question here: https://community.powerbi.com/t5/Desktop/Runnig-Division/m-p/1268413
If this helped, I would appreciate a Kudos!
If this resolves the issue, please mark it as a Solution! 🙂Hi Anonymous ,
Please try like this:
Create a month column:
Month = MONTH('Table'[date])Create measures:
Measure = VAR first_date = MINX ( FILTER ( ALL ( 'Table' ), 'Table'[date] <= MIN ( 'Table'[date] ) && 'Table'[Month] = MAX ( 'Table'[Month] ) ), 'Table'[date] ) VAR first_other_price = CALCULATE ( SUM ( 'Table'[other_price] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Product_id], 'Table'[Month] ), 'Table'[date] = first_date ) ) RETURN DIVIDE ( SUM ( 'Table'[other_price] ) * 100, first_other_price )AVG = AVERAGEX(ALLEXCEPT('Table','Table'[date]),[Measure])
Hi v-xuding-msft
Can we multiply it by sales before taking average, For example:
PRODUCT_ID date sale other_price
10 01-07-2020 100 10
10 02-07-2020 200 20
20 01-07-2020 300 30
20 02-07-2020 400 40
Befor sum of other price can we multiply by sales like AVERAGE(10*100,30*300)/Sum(100+300) for date 01/07/2020
Thanks
Hi Anonymous ,
Please try this:
sales * other_price = SUM('Table 1'[sales])* SUM('Table 1'[other_price])
AVG =
var avg_ = AVERAGEX(ALLEXCEPT('Table 1','Table 1'[date]),[sales * other_price])
var totalsales = CALCULATE(SUM('Table 1'[sales]),ALLEXCEPT('Table 1','Table 1'[date]))
return
DIVIDE(avg_,totalsales)