topic Graph doesn't place average per year correctly in DAX Commands and Tips

Graph doesn't place average per year correctly

0xygen27 — Wed, 08 Mar 2017 08:11:38 GMT

Dear community,

I am wondering what is wrong with my DAX formula or with my graph.

I made a graph with on the axis, date and on values I placed average per year.

The formula I am using to determine my average per year already got a date in it, it uses the date table.

I would like to get some help with figuring out where the problem is :)

average amount of unique visitors 2016 = CALCULATE(
AVERAGEX('amount of unique visitors','amount of unique visitors'[amount of unique visitors]),
FILTER(
ALL(Datedimension[Datekey]),
and (
'Datedimension'[Datekey]>= DATE(2016, 1 , 1),
'Datedimension'[Datekey]<= DATE(2016,12,31)
)
)
)

Thanks in advance :)

Re: Graph doesn't place average per year correctly

technolog — Sat, 26 Aug 2023 21:19:52 GMT

Can you please tell me if the problem is still relevant? If you managed to solve it in some way, please write here how you solved this problem. Then it will be possible to mark this problem as solved.

If not, it may make sense to ask the question again, thus confirming the relevance of this problem.

Re: Graph doesn't place average per year correctly

JoeBarry — Mon, 28 Aug 2023 08:59:33 GMT

Hi 0xygen27

Do you have two tables? One contains vistors and a date table? The date table is marked as a date table? The date table has a one to many relationship with the Vistor table on the date column on both tables?

You can try these.

Total Unique Vistors = DISTINCTCOUNT(Vistors[UserID])

or using the above measure

Total Unique Vistors YTD = TOTALYTD([Total Unique Vistors], DateTable[Date])

Create a matrix with the Year from the date table in wither column or row section and then add the either measure to the Value section to get desired result

Thanks

Joe

If this post helps, then please Accept it as the solution

If you need to unique vistors,

Re: Graph doesn't place average per year correctly

technolog — Mon, 28 Aug 2023 14:53:41 GMT

Hi!

Based on your description and the DAX formula provided, I see a couple of potential issues:

Naming Convention: The way you've named your tables and columns is a bit confusing. For instance, having a table named 'amount of unique visitors' and then a column within that table named '[amount of unique visitors]' can be misleading and cause potential mistakes.
AVERAGEX: This function averages the results of an expression calculated for each row in a table. It's important to ensure that the expression used inside AVERAGEX is accurately capturing what you want to average. In your case, you're just referencing the column, which might not be yielding what you intend.
ALL function: When using the ALL function in conjunction with FILTER, you're effectively removing any filters on the Datedimension[Datekey] and then reapplying them based on the conditions you specified in the FILTER function. It's not inherently wrong, but it's worth noting in case there are any other filters being applied elsewhere in your model that you would want to consider.

Let's try to address the problems and refine the formula:

AverageUniqueVisitors2016 = CALCULATE( AVERAGE('amount of unique visitors'[amount of unique visitors]), FILTER( ALL(Datedimension[Datekey]), Datedimension[Datekey] >= DATE(2016, 1 , 1) && Datedimension[Datekey] <= DATE(2016,12,31) ) )