How to show the monthly average instead of the row by row average in a table visual?

[antoniosantos]

Hello,

If I do 

AveragePrice = AVERAGE(PCMOV[CUSTOFIN])

and put it on a table visual, it will show the average on a date level, but I would like to know how to have it show the selected period average repeated in all rows instead.

This how it looks like. Note that the first row shows the monthly average, but that is because that summarizes the rows below, but I will need to compare each row's Avg Cost with that month's average and that is why I need a "static" value:

Thank you!

Best regards,

Antonio

[DOLEARY85]

Hi,

you can try using ALLEXCEPT to change the context filter and change it to just the month of a date field, try the below in a calculated column:

Column = CALCULATE(
    AVERAGE('Table Name'[Numeric Field]),ALLEXCEPT('Table Name','Table Name'[Date Field].[Month]))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[antoniosantos]

Thanks for responding wiht the example!
I appologize if I wasn't clear on the final result, but now it expands the table with all data and the average cost/month. I would like, however, the table to get filtered by the date slicer, containing a calendar table filtering the fact table by the date column.

How can I adjust the proposed solution?

I did update the question with the visual situation.

Thanks a million.

[Anonymous]

Hi @antoniosantos 

You can refer to the following example.

Sample data

Sample date table 

Relationship

Create a measure

Measure =
VAR a =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), [Type] IN VALUES ( 'Table'[Type] ) ),
        [Value]
    )
RETURN
    a

Then put the related field in a matrix visual

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.