calculated average not matching with Average line on visual

[Mous007]

Hi all,

So i have a small issue with averages and i can't figure the reason why as I want to report the same value on both visuals.

So in the following screen shot when i select a specific period the averages are matching on the card and graph.

The issue is when i have no specific period selected. i was expecting the same average result on the chart average line to be on the card if no period is selected  as i believe is the correct one after a quick check on excel. Please refer to the screenshot below to see the difference in results.

so my question is how can i alter my calculation to show the exact average as on the average line on the graph when no specific date is selected ?

Any help or hints would be really appreciated and if you need any extra information i would be happy to privde it .

Thanks in advance.

Mous

[Smauro]

Hi @Mous007 

You don't have a calculation issue, rather a "Power BI is kinda stupid" issue.

You see, the automatic average line is not a weighted average:

<u+202d>79.2   +   77.91   +   73   +   86.6   +   78.17   +   88.89 =<u+202c> 483.77
483.77   ÷   6 ~= 80.63

Whereas the total's calculation is..6 +

Cheerss the t

[v-xuding-msft · Accepted solution]

Hi @Mous007 ,

The following is my simple sample. You could reference it to modify yours.

Firstly, you need to create a new table and use date its column as the period slicer. 

Note: There is no relationship between the tables.

Example:

New date table = DISTINCT('Table'[Date])

Then create a measure like below:

(Please change the variable of avg_filtered  to your expression : % of requests without failures. )

Total_sales = SUM('Table'[Sales])


avg_date =
VAR avg_filtered =
    AVERAGE ( 'Table'[Sales] )
RETURN
    IF (
        ISFILTERED ( 'New date table'[Date] ),
        avg_filtered,
        AVERAGEX ( ALLSELECTED ( 'Table'[Date] ), [Total_sales] )
    )

[parry2k]

@Mous007 without knowing how your tables are related and what is your measure, it is not easy to answer the question. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490