Average of specific months or remove values from date Axis where data shouldn't be

[Daniel_B]

Hello

My question is 2 fold as I would like to both try and know both solutions for future endeavours

So... I have data which I want to compare quarterly however 1 data set only has a certain number of months per quarter and with the average measure I have built it is filling in quarters that do not exist with an average that are not correct so I would like to know the following

1. How do I create an average of values from 1 column based on the month they are in?

2. How do I suppress data where data should not be visible i.e. if quarter 2 has no data it should show as 0 and not pick up the previous quarter of data 

Thanks

[v-lionel-msft · Accepted solution]

Hi @Daniel_B ,

According to ypur description, I made a table like this:

And, I created a measure like this:

Avg. = 
VAR x=
CALCULATE(
    AVERAGE([Values]),
    ALLEXCEPT(
        Sheet7,
        Sheet7[Quarter]
    )
)
RETURN
IF(
    x = BLANK(),
    0,
    x
)

Best regards,
Lionel Chen

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