Forum Discussion

nishamartis's avatar
nishamartis
Frequent Visitor
9 years ago

DAX query for Average

Hello,   I need to calculate the average sale using the formula: [ProductA amount for a month] / [ProductB amount for a month] For example, for the Month of January 2016, ProductA amount = 1000 an...
Need Help
Datatouille's avatar
Datatouille
Solution Sage
9 years ago

Hi nishamartis

 

Well it is hard to help you without seeing the tables at stake but let's give it a try.

 

First, you need to have a proper Calendar Table to perform any Time Intelligence calculations.

Second, be aware that you don't need to select 2 years simultaneously. Selecting 1 year through a slicer and going 1 year backward using DAX will be good (unless you want to compare 2017 vs 2015... which would not make sense right?).

 

So, supposing the user always selects a single year and a single month, create the following measures:

Total Amount = Sum ( YourTable[Amount] )

 

AmountA = Calculate ( [Total Amount] , ProductTable[ProductType] = "A")

AmountALastY = Calculate ( [AmountA] , PreviousMonth(CalendarTable[Date] )

 

AmountB= Calculate ( [Total Amount] , ProductTable[ProductType] = "B")

AmountBLastY = Calculate ( [AmountB] , PreviousMonth(CalendarTable[Date] )

 

CustomAvg = If ( [AmountALastY] = 0 , Divide ( [AmountB] , [AmountBLastY] ) , If ([AmountBLastY] = 0, Divide ( [AmountA] , [AmountALastY] ) , Divide ( [AmountA] + [AmountB] , [AmountALastY] + [AmountBLastY]) )

 

You need to add another condition if both A and B were not sold last year but I am sure you got the logic :)

 

  • nishamartis's avatar
    nishamartis
    Frequent Visitor
    9 years ago

    Hi Datatouille

     

    Thanks for taking time out and replying to my query.

    My table structure is as follows:

    Both numerator and denominator is being taken as SUM(Amount)  where Amount is a column in two different fact tables(Fact1 and Fact2). Fact1 and Fact2 doesn't have a direct relationship between them but these two fact tables have a direct relationship with a Date table. This Date table is also used in my Year and Month slicer.

     

    The denominator is not 0 but missing value for some months in 2016. If I only select 2016, it will not show any rate since denominator is missing as expected. But if I select 2016 and 2017, it is calculating the rate as sum of two numerators(amount for 2016 and 2017) by the denominator of 2017(since 2016 denominator is missing for few months) which should not be happening. I want to ignore the 2016 numerator and calculate average of percentage across any date range. 

    I hope I am clear. How can I achieve this?