Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 oth...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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])

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • v-xuding-msft's avatar
      v-xuding-msft
      Community Support
      6 years ago

      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])

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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