- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- DAX query for Average

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

nishamartis

Frequent Visitor

DAX query for Average

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2017
05:19 AM

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 and ProductB amount = 10

for the Month of January 2017, ProductA amount = 2000 and ProductB amount = 20

Then if I select January as month and 2016 and 2017 as Year, average should be (1000 + 2000)/(10+20)

Consider the scenaio where I need to calculate the average for multiple months and one of the value in denominator is null:

for the Month of January 2016, ProductA amount = 1000 and ProductB amount = 0

for the Month of January 2017, ProductA amount = 2000 and ProductB amount = 20

Then average should be 2000/20. Need to ignore January 2016 sale amount since the denominator is 0.

Can anyone help me to resolve this.

Thanks in advance.

2 REPLIES 2

Datatouille

Established Member

Re: DAX query for Average

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2017
08:50 AM

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

Frequent Visitor

Re: DAX query for Average

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2017
11:02 PM

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?