Wrong average calculation per date filter selection in Power BI

[Analitika]

Hello,

I am calculating average of sum per period.

In visual I see this:

Two visuals and averages match, that correct. But when I select for example other case, for example,

 Average 670.51

And in another visual is:

So seems that averages don't match in this case. One assumption that average becomes wrong when period is not full year, like several months, etc.

Average in table is getting like this:

Bendra kaina_men vidurkis_new_ar_objektine =

DIVIDE(

[amount],[selected_month])

selected_month=

Pasirinkta menesiu =

SWITCH(

TRUE(),

ISFILTERED(_Date[Date].[Month]), IF(QUOTIENT(COUNT(_Date[Date].[Month]),30)=0,1,QUOTIENT(COUNT(_Date[Date].[Month]),30)),

IF(QUOTIENT(COUNT(_Date[Date].[Month]),30)=0,1,QUOTIENT(COUNT(_Date[Date].[Month]),30))

)

Date filter.

So how to solve my issue?

[Anonymous]

@Analitika , in your measure, you have this:

 _Date[Date].[Month]

What is the ".[Month]" part? When I use this in a measure, I get an error. Do you have a date hierarchy set up on your _Date.[Date] field?

[Anonymous]

@Analitika , Do both the Chart visual and the Table visual use the same measure for the Average?

Do both the Chart visual and the Table visual have the same filters?

[Analitika]

Yes date hierarchy created. Red line in visual is getting by average function. second visual use measure. Filters are same.

Average in first visual 

[Anonymous]

@Analitika , In the Chart visual the Average is 670.51. The chart is showing only eight months. If you multiply 670.51 by 8 you get a total amount of 5364.08.

If you divide 5364.08 by 12 you get 447.01, which is the average in the Table visual.

So it looks like your data only has values for 8 out of the 12 months, and the Chart Average function is averaging over only those 8 months. 

Your measure [selected month] is counting all the months in the slicer selected regardless if there is any Amound data for those months.

I would guess that in the first example where both visuals show the same average of 299.82 then there is Amount data for each of the 12 months.

I would suggest that you create a measure that gives you the desired output in all cases, and then use that measure in both the Chart and Table visuals.

[Analitika]

Ok, so how to fix my measure, I have no idea. I also changed measure to this measure:

DIVIDE([sum], DISTINCTCOUNT('_Date'[Month])), but it didn't work.

[Anonymous]

@Analitika , try changing the measure to DISTINCTCOUNT the month field from the table where the [sum] data comes from instead of the _Date table.

[Analitika]

Yes, I made distinctcount(date[month]) but getting wrong if I select two or more years in date slicer.

[Anonymous]

@Analitika , could you please post the DAX for the [sum] measure?

And what is the field in the fact table that has a relationship to the -Date table?