Forum Discussion
Running Division on First Date
Hello Experts,
I was trying to create measure in DAX on the following data:
Table Name: Sample
Product_id date My_price sales other_price Indexed other price
12345 20200701 100 75 120
12345 20200704 100 22 130
12345 20200705 100 56 140
12345 20200706 100 52 140
12345 20200731 100 99 140
12345 20200801 100 24 150
12345 20200809 100 58 120
12345 20200907 100 24 200
12345 20200908 100 39 120
12345 20200908 100 56 100
12345 20200908 100 64 125
Formula for Indexed other price=other_price/first_other_price_in_the_month * 100
Below is expected output:
Product_id date My_price sales other_price Indexed other price
12345 20200701 100 75 120 100
12345 20200704 100 22 130 108.3333333
12345 20200705 100 56 140 116.6666667
12345 20200706 100 52 140 116.6666667
12345 20200731 100 99 140 116.6666667
12345 20200801 100 24 150 100
12345 20200809 100 58 120 80
12345 20200907 100 24 200 100
12345 20200908 100 39 120 60
12345 20200908 100 56 100 50
12345 20200908 100 64 125 62.5
In above example For:
July Month first_other_price_in_the_month=120 (to be used in formula for july month data)
August Month first_other_price_in_the_month=150 (to be used in formula for Augmonth data)
September Month first_other_price_in_the_month=200 (to be used in formula for Sept month data)
Note: I have 100 different Product_ID, in above example have considered only one product_id.
Any help or suggestion on DAX code for populating indexed other price would be highly appreciated.
Thanks
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])
5 Replies
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! 🙂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])