topic Re: AVERAGEX when filtered by Date in DAX Commands and Tips

AVERAGEX when filtered by Date

czaldumbide — Thu, 06 Feb 2020 16:41:56 GMT

Hello Friends,

I am using the function AVERAGEX to calculate the daily average amount of incoming wires. I understand that this type of measures are meant to be used in a monthly context or upwards, but I was wondering what happens when used in a daily context.

For example, this measure will be used in a dashboard where I have Transaction Date as one of the filters the users can change. I was wondering what happens to this value if the user selects a single day in the Transactions Date filter. From what I have seen, the measure does not display the avergae for that day, nor the total (as the amount displayed is higher than the total for that day).

My function looks like this:

Avg Daily Amount Incoming =

AVERAGEX(

VALUES('Wires Table'[TransactionDate].[Date]),

CALCULATE(SUM('Wires Table'[Amount]), 'Wires Table'[Type] = "Incoming")

)

I would appreciate any explanations as to what it is displaying and why.

Thank you!

Re: AVERAGEX when filtered by Date

Anonymous — Fri, 07 Feb 2020 18:47:07 GMT

Hi czaldumbide ,

Averagex is not specifically meant to be used for monthly calculation, it can calculate an average of anything.

To answer you question is much easier if you should some (sample) data, relationships (if any) and expected result.

regards,

jan

Re: AVERAGEX when filtered by Date

amitchandak — Sat, 08 Feb 2020 13:44:17 GMT

Refer if this can help

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -Decoding Direct Query - Time Intelligence , Winner Coloring on MAP , >HR Analytics , Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

Re: AVERAGEX when filtered by Date

hafizsultan — Sat, 08 Feb 2020 23:55:26 GMT

You can try below:

Avg Daily Amount Incoming =

AVERAGEX(

KEEPFILTERS(VALUES('Wires Table'[TransactionDate].[Date])),

CALCULATE(SUM('Wires Table'[Amount]), 'Wires Table'[Type] = "Incoming")

)

Re: AVERAGEX when filtered by Date

czaldumbide — Thu, 13 Feb 2020 15:54:23 GMT

Hi Anonymous

Let me give you a little bit more context. I have a table called 'Wires' that have the columns of Amount, Type and Transaction Date. I have a date table called 'Date Filter' that has a Date column. These tables are related ( One to many, single direction) by Date and Transaction Date.

Given this, I want to calculate the average of incoming wires. Basically I just want to sum up all Amount of type Incoming and divide by the DISTINCTCOUNT of date.

Ex:

Avg Daily Amount Incoming =

var totalincoming = CALCULATE(SUM('Wires'[Amount]), FILTER('Wires', 'Wires'[Type] = "Incoming"))

var totaldates = DISTINCTCOUNT('Date Filter'[Date].[Date])

return DIVIDE(totalincoming, totaldates)

Filter Date will be used as a filter in my dahsboard. So I want the result of my measure to be the average of only the selected range, or in the case of a single date selected then the total amount incoming for that date.

Let me know if that's enough information. All my data si quite sensitive so it is difficult to provide sample data.

Thanks!

Re: AVERAGEX when filtered by Date

czaldumbide — Thu, 13 Feb 2020 15:59:12 GMT

Hi hafizsultan and amitchandak

Thanks for your suggestion. It is still not giving me the answer I'm looking for. I basically need to calculate an average that when filtered by a range of dates it divides the sum by the distinct count of dates in that range and when selected a single date it will provide me the total amount for that particular date.

You can see the response I gave to Anonymous for a little bit more context.

Thanks!