Forum Discussion
average
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!
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)
Hi ianbarker
Was happy to hearing by your side.Might be this sample date clarify you what calculation i need
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
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.
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!