Formula result as per the selected month filter

[youssefm9]

I would like to do a formula. 

If I select a certain month in the filter, then it will provide the sum of sales during that month.

However, If i select all months in the filter, I want to get the result of all months divided by 12.

How can I get the result for the both scenarios depending on what I select on the months filter?

Thanks,

Youssef

[v-xinruzhu-msft]

Hi @youssefm9 

You can create a measure like this:

_average = var a=CALCULATE(COUNTAX(DISTINCT('Table'[Month]),[Month]),ALLSELECTED('Table'))
return SUMX(ALLSELECTED('Table'),[Value])/a

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.

[youssefm9]

Hi @v-xinruzhu-msft 

I have added the word Calculate as per the below and it seems it works... is that logic?

Average ACV = AVERAGEX(DISTINCT('P&L Data'[Month]), calculate(sum('P&L Data'[ACV])))

[ppm1]

You can create a measure like this one, which will give the average of all months selected.

Monthly Avg = AVERAGEX(DISTINCT(Table[MonthColumn]), [YourMeasure])

Pat

[youssefm9]

It is not what i am asking for.

What I am asking for is, if any month is selected, then give me the total. If no month is selected (thus all months selected together), give the the total divided by 12.

[ppm1]

Did you try the measure to see if it works? If 1 month selected it will give you the total for that month. If >1 month selected (or all selected), it will give you the average total. Note that a measure is needed (not just SUM, unless it is wrapped in CALCULATE).

Pat

[youssefm9]

I tried the below but did not work.

Average ACV = AVERAGEX(DISTINCT('P&L Data'[Month]), SUM('P&L Data'[ACV]))

where ACV is the amount. Table is P&L Data. 

[bolfri]

Ok, then to be more strict:

• what if user select only two months - do you want to show sum of 2 months divided by 12 (which makes no sanse)? 😄 or maybe 3 months? still - divided by 12?

OR

do you want:

* if month is no filtered - give me sum of some value

* if month is filtered - give me sum of some value for selected month divided by count of selected month?

[youssefm9]

Hey thank you for the reply !

I would go for: * if month is filtered - give me sum of some value for selected month divided by count of selected month?

[bolfri]

In that case @ppm1 gave you correct answer. For one month it will return the value of this month, but for two it will return the sum of them and divided by 2, which is basicly an average. 🙂

Monthly Avg = AVERAGEX(DISTINCT(Table[MonthColumn]), SUM([Some Value]))

[youssefm9]

Hello...

Thank you for the reply.

However it is not working from my side. It works if I select one month only. If I select more than one month, it will do sum.

Below is the formula i am using:

Average ACV = AVERAGEX(DISTINCT('P&L Data'[Month]), SUM('P&L Data'[ACV]))

where ACV is the amount. Table is P&L Data. 

[bolfri]

Can you post here an example data to see your issue?

[youssefm9]

Hi @bolfri 

I have added the word Calculate as per the below and it seems it works... is that logic?

Average ACV = AVERAGEX(DISTINCT('P&L Data'[Month]), calculate(sum('P&L Data'[ACV])))

[bolfri · Accepted solution]

No, you don't need calculate here.

Average ACV = AVERAGEX(DISTINCT('P&L Data'[Month]),sum('P&L Data'[ACV]))