Runnig Division

[Anonymous]

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

[edhans · Accepted solution]

You need a date table to do this properly @Anonymous which I've included in the PBIX file I'm sharing below. This measure will I think return what you want.

Measure = 
VAR varCurrentDate =
    MAXX(
        'Table',
        RELATED('Date'[Date])
    )
VAR varCurrentMonthYear =
    YEAR( varCurrentDate )
        * 100
            + MONTH( varCurrentDate )
VAR varCurrentMonth =
    MAXX(
        'Table',
        RELATED( 'Date'[Month Year Sort] )
    )
VAR varFirstDateOfMonth =
    CALCULATE(
        MIN( 'Table'[date] ),
        REMOVEFILTERS( 'Table'[date] ),
        FILTER(
            'Date',
            'Date'[Month Year Sort] = varCurrentMonthYear
        )
    ) 
VAR varFirstOtherPrice =
    CALCULATE(
        MAX( 'Table'[other_price] ),
        'Table'[date] = varFirstDateOfMonth,
        REMOVEFILTERS( 'Table'[date] )
    ) 
VAR varOtherPrice =
    MAX( 'Table'[other_price] )
VAR Result =
    DIVIDE(
        varOtherPrice,
        varFirstOtherPrice,
        0
    ) * 100
RETURN
    Result

My PBIX file is here.

[edhans · Accepted solution]

You need a date table to do this properly @Anonymous which I've included in the PBIX file I'm sharing below. This measure will I think return what you want.

Measure = 
VAR varCurrentDate =
    MAXX(
        'Table',
        RELATED('Date'[Date])
    )
VAR varCurrentMonthYear =
    YEAR( varCurrentDate )
        * 100
            + MONTH( varCurrentDate )
VAR varCurrentMonth =
    MAXX(
        'Table',
        RELATED( 'Date'[Month Year Sort] )
    )
VAR varFirstDateOfMonth =
    CALCULATE(
        MIN( 'Table'[date] ),
        REMOVEFILTERS( 'Table'[date] ),
        FILTER(
            'Date',
            'Date'[Month Year Sort] = varCurrentMonthYear
        )
    ) 
VAR varFirstOtherPrice =
    CALCULATE(
        MAX( 'Table'[other_price] ),
        'Table'[date] = varFirstDateOfMonth,
        REMOVEFILTERS( 'Table'[date] )
    ) 
VAR varOtherPrice =
    MAX( 'Table'[other_price] )
VAR Result =
    DIVIDE(
        varOtherPrice,
        varFirstOtherPrice,
        0
    ) * 100
RETURN
    Result

My PBIX file is here.

[Antara]

Thanks @edhans  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

[v-yiruan-msft · Accepted solution]

Hi @Antara ,

You can try to create a measure as below:

avg_measure =
VAR _sumofSales =
    SUMX (
        FILTER (
            ALL ( 'Table'[date], 'Table'[Product_id] ),
            'Table'[date] = MAX ( 'Table'[date] )
        ),
        [Measure]
    )
VAR _countofP =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Product_id] ),
        FILTER ( 'Table', 'Table'[date] = MAX ( 'Table'[date] ) )
    )
RETURN
    DIVIDE ( _sumofSales, _countofP, 0 )

Best Regards

Rena