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 oth...
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

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