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])
Thanks UAV-Test-Pilot for the quick response. I really appreciate. If in case I want perform average on top of measure across date (irrespective of Product_id). Can we create average measure on top of this measure?
In short,
To show date, avg_of_Measure_created_below in a table visual. If we have date as 20200701 for 2 Product_ID(say 12345 and 56789) and Measure(created using formula mention by @edhans ) as value 100 and 200 then table visual should show:
Output:
date avg_measure
20200701 150
Any help or suggestion would be helpful
Thanks
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
ThanksHi 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)