Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Runnig Division

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

Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    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

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        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