Creating Slicer

[Anonymous]

I have data that looks like this:

 

What I'm aiming to do is create a slicer that is based on the average of the estimated annual revenue column, grouped by year. I've found this tricky to do because when trying to get the average, it returns the exact same value as estimated annual revenue so I see that it is calculating based on a row by row basis.

I can get what I'm looking for with a measure, but a measure cannot be placed onto a slicer. The only other idea I had was maybe creating tables for each year with the average for each person and appending them all together but I'm unsure of how to even do that. Anyone know of any ways around this?

[judspud]

Hi @Anonymous ,

Please see the following column formula;

Average =

var year = year(Table1[CloseDate])

return

CALCULATE(SUM(Table1[Estimated Annual Revenue]),year(Table1[CloseDate])=year)/CALCULATE(COUNT(Table1[Estimated Annual Revenue]),year(Table1[CloseDate])=year)

Please let me know if this meets your requirements 🙂

Thanks,

George

[v-piga-msft]

Hi @Anonymous ,

I have a little confused about your requirement. Do you want to create a slicer with the average of the estimated annual revenue?

Is the average of the estimated annual revenue a measure?

If it is convenient, could you share your desired output so that we could help further on it.

In addition, you could have a reference of this blog and the similar thread.

Best  Regards,

Cherry

[Anonymous]

hi @v-piga-msft 

I was hoping this could be done through a column so I can place the column on a slicer since measures cannot be placed on a slicer. I have the DAX written through a measure and it works as intended, but when I create the exact same DAX through a column it just returns the same values as the estimated annual revenue which I already have.

I unfortunately cannot share my file because of the data sensitivity.

[v-piga-msft]

Hi @Anonymous ,

Did the formula from judspud solve your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[Anonymous]

@v-piga-msft I was never able to figure it out. I'm just not sure if it's even possible or not, just thought I'd ask. I unfortunately cannot share because of data sensitivity and credentials needed to view the data.