Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎07-13-2016
Accepted Solution

average

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.


Accepted Solutions
Regular Visitor
Posts: 38
Registered: ‎06-08-2016

Re: average

Hi @orxanbabashov

 

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!

 

 

 

 

View solution in original post


All Replies
Regular Visitor
Posts: 38
Registered: ‎06-08-2016

Re: average

Hi @orxanbabashov

 

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)

Frequent Visitor
Posts: 4
Registered: ‎07-13-2016

Re: average

Hi ianbarker

 

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

 

123.PNG

Regular Visitor
Posts: 38
Registered: ‎06-08-2016

Re: average

Hi @orxanbabashov

 

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

Frequent Visitor
Posts: 4
Registered: ‎07-13-2016

Re: average

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.

Capture.PNG

 

 

 

 

Regular Visitor
Posts: 38
Registered: ‎06-08-2016

Re: average

Hi @orxanbabashov

 

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!