Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBI_newuser
Post Prodigy
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

 

PBI_newuser_0-1624525690094.png

 

2 ACCEPTED SOLUTIONS

Hi, @PBI_newuser 

 

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

vjaneygmsft_0-1624936664733.png

You can try this:

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

 

vjaneygmsft_1-1624937297386.png

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.

View solution in original post

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]
        )
    )
)

vjaneygmsft_0-1625033612277.png

 

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.

View solution in original post

19 REPLIES 19
v-janeyg-msft
Community Support
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

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.

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

PBI_newuser_0-1624931521299.png

 

Hi, @PBI_newuser 

 

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

vjaneygmsft_0-1624936664733.png

You can try this:

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

 

vjaneygmsft_1-1624937297386.png

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.

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.
PBI_newuser_0-1624952264932.png

 

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.

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

PBI_newuser_0-1624954124482.png

 

@PBI_newuser 

 

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

@v-janeyg-msft 

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

 

PBI_newuser_0-1624958276142.png

 

@PBI_newuser 

 

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.

vjaneygmsft_0-1624958644269.png

 

@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.

PBI_newuser_0-1624960988762.png

 

😶@PBI_newuser 

 

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

vjaneygmsft_0-1624962976299.png

 

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

 

Product Volume Impact
B

-3262

C-331
D-3192

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]
        )
    )
)

vjaneygmsft_0-1625033612277.png

 

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.

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.

 

PBI_newuser_1-1625040421776.png

 

PBI_newuser_0-1625040353261.png

 

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

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.