cancel
Showing results for
Did you mean:
Post Prodigy

Average of Sum of ratio per month Last Year

Hi, I wanna calculate the average of last year sum but it seems not working with the formula below. Please help.

Sample here.

AUSP Annual =
AVERAGEX(
SUMMARIZE('Append Table','Calendar'[YearMonth]),
CALCULATE([AUSP]))*12

AUSP Annual Last Year =
AVERAGEX(
SUMMARIZE('Append Table','Calendar'[YearMonth]),
CALCULATE([AUSP Last Year]))*12

2 ACCEPTED SOLUTIONS
Community Support

Hi, @PBI_newuser

You can see that the table don't have over 2019-3 dates,so the value is blank.

You can try this:

AUSP Annual Last Year =
CALCULATE([AUSP Annual],SAMEPERIODLASTYEAR('Calendar'[Date]))

Best Regards

Janey Guo

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

Community Support

Hi, @PBI_newuser

Try:

Volume Impact =
IF (
MIN ( 'Calendar'[Date] ) > MAX ( 'Append Table'[Date] ),
CALCULATE (
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ),
IF (
[Volume Last Year] <= 0,
( [Revenue Current Year] - [Revenue Last Year] ),
( [Volume Current Year] - [Volume Last Year] ) * [AUSP Last Year]
)
),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ),
IF (
[Volume Last Year] <= 0,
( [Revenue Current Year] - [Revenue Last Year] ),
( [Volume Current Year] - [Volume Last Year] ) * [AUSP Last Year]
)
)
)

Best Regards

Janey Guo

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

19 REPLIES 19
Community Support

Hi, @PBI_newuser

According to your description, [AUSP Last Year] should be a measure, can you share the formula because the formula you provided has no no syntax errors? If you can share some sample fake data we can help you soon.

Best Regards

Janey Guo

Post Prodigy
Community Support

Hi, @PBI_newuser

I can see the file, Can you show me your desired result like? I need to rewrite the code according to your logic and result.

Post Prodigy

Hi @v-janeyg-msft , i want the same result for "AUSP Annual" in FY2019 show up under "AUSP Annual Last Year" field in FY20.

Community Support

Hi, @PBI_newuser

You can see that the table don't have over 2019-3 dates,so the value is blank.

You can try this:

AUSP Annual Last Year =
CALCULATE([AUSP Annual],SAMEPERIODLASTYEAR('Calendar'[Date]))

Best Regards

Janey Guo

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

Post Prodigy

Hi @v-janeyg-msft , it works! Thank you so much! 😊

I have one more question. How to set those blank cell to 0?

For the below measure, I couldn't calculate my "Volume Impact" correctly.
If the [Volume Current Year] = Blank, set it as 0 so that it can minus [Volume Last Year].

Here is the sample.

Volume Impact =
SUMX(
SUMMARIZE('Append Table','Calendar'[YearMonth]),
IF([Volume Last Year]<=0,([Revenue Current Year]-[Revenue Last Year]),
([Volume Current Year]-[Volume Last Year])*[AUSP Last Year]))
This is the sample of expected output.

Community Support

Try :

new measure=if([column current year]=blank(),0,[column current year])

Best Regards

Janey Guo

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

Post Prodigy

Hi @v-janeyg-msft , I tried but it doesn't work.

Community Support

(12-11) *389 is not equal to 4362. What result do you want?

Post Prodigy

Not (12-11) but (0-7) as shown in the screenshot below.

Community Support

There is a problem with your measure, I said before.

What do you want the following three values? I can only restore the results you need as much as possible.

Post Prodigy

@v-janeyg-msft , I am so sorry, i misunderstood your question.

I want to calculate the volume impact in each month then sum it up as total for the selected FY.

For Product B, C & D, the sum of volume impact for the year should be 0.

Community Support

Please tell me what value should be displayed in these three lines.

Post Prodigy

@v-janeyg-msft , below values should be displayed in the table. Thanks.

 Product Volume Impact B -3262 C -331 D -3192
Community Support

Hi, @PBI_newuser

Try:

Volume Impact =
IF (
MIN ( 'Calendar'[Date] ) > MAX ( 'Append Table'[Date] ),
CALCULATE (
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ),
IF (
[Volume Last Year] <= 0,
( [Revenue Current Year] - [Revenue Last Year] ),
( [Volume Current Year] - [Volume Last Year] ) * [AUSP Last Year]
)
),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ),
IF (
[Volume Last Year] <= 0,
( [Revenue Current Year] - [Revenue Last Year] ),
( [Volume Current Year] - [Volume Last Year] ) * [AUSP Last Year]
)
)
)

Best Regards

Janey Guo

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

Post Prodigy

Hi @v-janeyg-msft , sorry to bother you, I have one last question.
I tried to modify your measure by multiplying (-1) as I wanna show negative values for Volume Impact if [Volume Current Year] = 0. I noticed some of the products have incorrect sum of [Volume Impact].

For example, for Product E, the [Volume Impact] for each month is correct. But when it sum up for FY2020, it became incorrect. It should be -28022 instead of -13369. I couldn't locate the error. Could you please help me? Thank you so much!

Here is the sample.

Community Support

Hi,  @PBI_newuser

This is not a simple problem. The root cause of the problem is the mismatch of date data. You have to modify more conditions to solve it. But I don't have time to check it now, I will reply to you when I am free, or you can choose to open a new case.

Best Regards

Janey Guo

Post Prodigy

Thank you so much for your help @v-janeyg-msft !

Post Prodigy

Thank you so much @v-janeyg-msft !!! 😀

Announcements

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.