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

# average

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

07-13-2016 09:53 PM

Hello everybody

Hope so you are doing well.

I am so new to Power BI and have some difficulties solving some calculation using DAX funtional language.My question is that i wanna to calculate average Debt for Day.

Lets assume that for the first day of month 5000$ debt,next two days total 7000$,next three day total 10000$ debt so far, it is demanded from me to calculated daily average debt.

If there is any difficulties to understand my question,plz ask me

I would appreciate your help

Thanks so much in advance.

Solved! Go to Solution.

Accepted Solutions

## Re: average

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

07-14-2016 06:28 PM

Based on your table, the average debt for the 8 days is 80 and not 16.25.

It looks like you are trying to calculate the daily difference in debt and then create an average of that.

Do you already have the Daily Debt value in your data set or are you looking to calculate it using DAX?

If you do, then it is a simple case of creating the same measure I mentioned before but based on the Daily Debt field like:

Average Daily Debt = SUM('TableName'[Daily Debt])/COUNTROWS('TableName')

If you are also looking to create the Daily Debt field then this complicates things a little more.

The way I would do it is by first creating an index column in your data.

Easiest way to do this is by clicking your Edit Queries button, click on the Add Column tab in the ribbon, and then click Add Index Column.

Each row will then have a uniqe id.

Then close and apply this change.

Then click on New Column and create a column with the following formula:

Daily Debt = IF(Sheet1[Date]=LOOKUPVALUE(Sheet1[Date],Sheet1[Index],Sheet1[Index]-1),0,Sheet1[Debt]-LOOKUPVALUE(Sheet1[Debt],Sheet1[Index],Sheet1[Index]-1))

This formula is basically looking at the value on the previous row in the debt column and working out the difference between the two rows.

Once you have this, you can create your average measure as before.

There may be other ways to do this, but I tested it with your dataset and it worked for me!

All Replies

## Re: average

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

07-14-2016 12:36 AM

We would probably need to see a sample set of your data, but a simple method is to create a MEASURE that looks something like:

AVERAGE = SUM(DebtValue)/COUNTROWS(TableName)

## Re: average

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

07-14-2016 12:55 AM

Hi ianbarker

Was happy to hearing by your side.Might be this sample date clarify you what calculation i need

## Re: average

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

07-14-2016 01:02 AM

You wouldn't create the Daily Average Debt as a new column, because you only have a single value on each row, and you aren't really going to create a useful average from a single value.

You need to create a Measure that sits in the same table.

The Measure formula would be more or less the same as what I said in my previous post. Something along the lines of:

AVERAGE = SUM('Table Name'[Debt])/COUNTROWS('Table Name')

This will give you a measure that you can use in your report. It will automatically give you the correct average based on any other filters you have applied to the report, such as date range, etc.

Regards,

Ian

## Re: average

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

07-14-2016 02:31 AM

Hi @ianbarker

Yeap i really agree with your thought for calculation to choose measure or column.Plz lets see i show here my average daily debt and got result for 8days in Excel, but in DAX i do not know how to right function.I did your calculation in DAX but did not get result which i want.

## Re: average

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

07-14-2016 06:28 PM

Based on your table, the average debt for the 8 days is 80 and not 16.25.

It looks like you are trying to calculate the daily difference in debt and then create an average of that.

Do you already have the Daily Debt value in your data set or are you looking to calculate it using DAX?

If you do, then it is a simple case of creating the same measure I mentioned before but based on the Daily Debt field like:

Average Daily Debt = SUM('TableName'[Daily Debt])/COUNTROWS('TableName')

If you are also looking to create the Daily Debt field then this complicates things a little more.

The way I would do it is by first creating an index column in your data.

Easiest way to do this is by clicking your Edit Queries button, click on the Add Column tab in the ribbon, and then click Add Index Column.

Each row will then have a uniqe id.

Then close and apply this change.

Then click on New Column and create a column with the following formula:

Daily Debt = IF(Sheet1[Date]=LOOKUPVALUE(Sheet1[Date],Sheet1[Index],Sheet1[Index]-1),0,Sheet1[Debt]-LOOKUPVALUE(Sheet1[Debt],Sheet1[Index],Sheet1[Index]-1))

This formula is basically looking at the value on the previous row in the debt column and working out the difference between the two rows.

Once you have this, you can create your average measure as before.

There may be other ways to do this, but I tested it with your dataset and it worked for me!