YOY sales growth & ranking on highest sales in 1 column

[Anonymous]

Hi!

With the help of the people on this platform I have almost finished my dashboard. But I have 2 last questions.

1) How can I calculate year on year sales growth? 

This is my dataset (I don't know how to add it as an attachment):

Fiscal YearQuarterMonthFiscal Week NumberBusiness unitItem numberSalesWeek & Year

2019	1	January	1	Benelux	A1234	1000	20191
2019	1	January	2	Benelux	A5678	800	20192
2019	1	January	1	Nordic	A1234	1100	20191
2019	1	January	2	Nordic	A5678	1200	20192
2019	1	March	10	Benelux	A1357	600	201910
2019	1	March	11	Benelux	A5678	1500	201911
2019	1	March	10	Nordic	A1234	900	201910
2019	1	March	11	Nordic	A5678	1300	201911
2019	2	June	22	Benelux	A1357	1400	201922
2019	2	June	23	Benelux	A5678	500	201923
2019	2	June	22	Nordic	A1234	300	201922
2019	2	June	23	Nordic	A5678	800	201923
2019	4	December	48	Benelux	A1234	1400	201948
2019	4	December	49	Benelux	A1357	300	201949
2019	4	December	48	Nordic	A1234	1500	201948
2019	4	December	49	Nordic	A5678	1100	201949
2020	1	January	1	Benelux	A1234	300	20201
2020	1	January	2	Benelux	A5678	1500	20202
2020	1	January	1	Nordic	A1234	1100	20201
2020	1	January	2	Nordic	A1357	1000	20202
2020	1	March	10	Benelux	A1234	800	202010
2020	1	March	11	Benelux	A5678	500	202011
2020	1	March	10	Nordic	A1234	1200	202010
2020	1	March	11	Nordic	A5678	600	202011
2020	2	June	22	Benelux	A1234	1000	202022
2020	2	June	23	Benelux	A5678	900	202023
2020	2	June	22	Nordic	A1234	1000	202022
2020	2	June	23	Nordic	A1357	1200	202023
2020	4	December	48	Benelux	A1234	800	202048
2020	4	December	49	Benelux	A5678	400	202049
2020	4	December	48	Nordic	A1234	800	202048
2020	4	December	49	Nordic	A5678	1100	202049
2021	1	January	1	Benelux	A1234	600	20211
2021	1	January	2	Benelux	A5678	1100	20212
2021	1	January	1	Nordic	A1234	900	20211
2021	1	January	2	Nordic	A1357	800	20212
2021	1	March	10	Benelux	A1357	1200	202110
2021	1	March	11	Benelux	A5678	500	202111
2021	1	March	10	Nordic	A1234	800	202110
2021	1	March	11	Nordic	A1357	800	202111
2021	2	June	22	Benelux	A1357	1400	202122
2021	2	June	23	Benelux	A5678	500	202123
2021	2	June	22	Nordic	A1234	1500	202122
2021	2	June	23	Nordic	A1357	900	202123
2021	4	December	48	Benelux	A1234	1200	202148
2021	4	December	49	Benelux	A5678	800	202149
2021	4	December	48	Nordic	A1357	1400	202148
2021	4	December	49	Nordic	A5678	300	202149

This is the matrix I have build so far:

How can I make an index on the years to be able to see the growth (or decline) between the years?

2) With the same dataset I have build this matrix:

Here I would like to rank the items based on highest sales of 2021. So in the end it should look like this (made in excel):

Can someone help me with this?

Thanks a lot!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Sorry for that I have ignored each BU growth%, change the first formula from all() to allexcept() to get the right result:

growth% = 
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLEXCEPT(  'Table' ,'Table'[Business]),
            'Table'[Fiscal Year]
                = CALCULATE (
                    MAX ( 'Table'[Fiscal Year] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Year] < MAX ( 'Table'[Fiscal Year] ) )
                )
        )
    )
VAR _sales =
    SUM ( 'Table'[Sales] )
VAR _presales =
    IF ( ISBLANK ( _previous ), SUM ( 'Table'[Sales] ), _previous )
RETURN
    DIVIDE ( _sales - _presales, _presales, 0 )

For the ranking issue, you can create a measure like this, put it in the matrix and sort by this measure:

Salessum = 
IF(
    HASONEVALUE('Table'[Fiscal Year]),SUM('Table'[Sales]),
    CALCULATE(SUM('Table'[Sales]),'Table'[Fiscal Year] = 2021)
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ERD]

Hi @Anonymous ,

Can you, please, show the result you want to achieve (in terms of the provided dataset). I believe there supposed to be images in the description, but I don't see them.

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

[Anonymous]

Hi @ERD,

I left another message after I tried the solution v-yingjl gave. I will copy it below:

I am already a bit further but I am not there yet. It is strange that the visuals were not visible which made it not completely clear what I wanted.

About the first issue, the YOY sales I was able to create what you showed. But what I want to see is the following example (made in excel):

So I want to have the business units below each other and then an index for the last 2 years. If I try this in Power BI this is the result:

The index for total is correct but for the BU it doesn't make sense. Besides that I don't want to see the first (yellow) index. Is there a way how I can solve this?

For the second question: this doesn't really work when I do this in Power BI. This is the result I get:

Rank is 10 for every line. This is the result I would like to get:

So, 3 years (or 2 if that is easier) next to each other and then the items are ranked on the highest item in 2021. Is this possible?

I hope you can see the visuals now.

Thanks!

[ERD]

@Anonymous ,

Is this what you want to achieve? I've played with the data you've provided (please, keep in mind that it is much better to have a proper Calendar/Date table and use it for such calculations. Hope you have it in your real data scenario).

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

[Anonymous]

Hi @ERD 

I know it is better with a proper date table, but the date doesn't come out of the system. So I don't have it in the dataset. The lowest date level I have is weeknumber.

The first table is indeed what I would like to have. But if I calculate it, the YOY sales % for Benelux is 17.7% and for Nordic -7.5%. So it looks like to business units are not correct but the total is correct. 

The second table is not exactly how I want to have it. I want to have the years as columns so yuo only see A1234 once in the table and then the sales for 2020 and 2021 next to it. And then I want to have it ranked on 2021 sales. Like this:

Would be great if you can help me!

Thanks,

Maartje

[ERD]

@Anonymous ,

Sorry, put wrong measure into the formula. Numbers should be correct:

There is a trick to achieve a second part, but you still need a measure with ranking for that.

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

[Anonymous]

Hi @ERD 

Both graphs look good! Can you let me know how you did it? I don't see it anywhere.

Many thanks!!

Maartje

[ERD · Accepted solution]

@Anonymous ,

The measure I've used:

growth% = 
VAR prevYear =
    CALCULATE (
        MAX ( 'Table4'[Fiscal Year] ),
        FILTER (
            ALL ( 'Table4' ),
            'Table4'[Fiscal Year] < MAX ( 'Table4'[Fiscal Year] )
        )
    )
VAR prevSum =
    CALCULATE (
        SUM ( 'Table4'[Sales] ),
        FILTER (
            ALLEXCEPT ( 'Table4', 'Table4'[Business unit] ),
            'Table4'[Fiscal Year] = prevYear
        )
    )
VAR CYSales = SUM ( 'Table4'[Sales] )
VAR PYSales = COALESCE ( prevSum, SUM ( 'Table4'[Sales] ) )
VAR YOYSales = CYSales - PYSales
VAR RES = DIVIDE ( YOYSales, PYSales, 0 )
RETURN RES

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

[Anonymous]

Hi @ERD ,

I have tried the formula and in my testfile (the one I showed in this topic) it is working. But in my actual file (which contains a lot of sensitive information so I can't share it), I get the wrong indexes.

I received another formula of somebody else and it gave the same problem (also exactly the same numbers). Any idea why this is not working with this dataset?

Thanks a lot!

Maartje

[v-yingjl]

Hi @Anonymous ,

You can extract the previous year formula as a single measure to check whether its value is correct if the table structure is the same.

    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLEXCEPT(  'Table' ,'Table'[Business]),
            'Table'[Fiscal Year]
                = CALCULATE (
                    MAX ( 'Table'[Fiscal Year] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Year] < MAX ( 'Table'[Fiscal Year] ) )
                )
        )
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ERD]

@Anonymous ,

I can't see the particularities of your real data, but I would try to check the transitional steps (put prevYear, prevSum, CYSales (current year sales), etc into RETURN and analyze the results you see.

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

[Anonymous]

@ERD 

I figured out what the issue is. In my real file I have a filter on weeknumber. Because I don't have a full year for 2021 yet, I want to compare 2021 until week 21 with 2020 until week 21. But for 2020 it takes the full year, eventhough I have selected only until week 21. 

Is there a way that I can only select specific weeks to compare?

Thanks again!

[ERD]

@Anonymous ,

I'm able to filter your demo data by week:

But with such scenario in your real data there might be issues.

Do you have Date field in your Data table?

Do you have a proper Calendar table?

[v-yingjl]

Hi @Anonymous ,

Since have not seen the pictures , you can try these measures:

For the year growth%, you can create this measure:

growth% = 
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Fiscal Year]
                = CALCULATE (
                    MAX ( 'Table'[Fiscal Year] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Year] < MAX ( 'Table'[Fiscal Year] ) )
                )
        )
    )
VAR _sales =
    SUM ( 'Table'[Sales] )
VAR _presales =
    IF ( ISBLANK ( _previous ), SUM ( 'Table'[Sales] ), _previous )
RETURN
    DIVIDE ( _sales - _presales, _presales, 0 )

For the 2021 sales rank, I would suggest you to use table visual to show because matrix would only show the aggreated value. Try this measure:

rank =
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Year] = 2021 ),
    'Table'[Sales],
    CALCULATE ( SUM ( 'Table'[Sales] ) ),
    ASC,
    DENSE
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-yingjl !

I am already a bit further but I am not there yet. It is strange that the visuals were not visible which made it not completely clear what I wanted.

About the first issue, the YOY sales I was able to create what you showed. But what I want to see is the following example (made in excel):

So I want to have the business units below each other and then an index for the last 2 years. If I try this in Power BI this is the result:

The index for total is correct but for the BU it doesn't make sense. Besides that I don't want to see the first (yellow) index. Is there a way how I can solve this?

For the second question: this doesn't really work when I do this in Power BI. This is the result I get:

Rank is 10 for every line. This is the result I would like to get:

So, 3 years (or 2 if that is easier) next to each other and then the items are ranked on the highest item in 2021. Is this possible?

Thanks a lot!

Maartje

[v-yingjl]

Hi @Anonymous ,

For question 1:

If the total does not make sense, you can just disable the total feature in the visual.

If you want to only show the last total, you can drag the column spacing manually:

For question 2:

Based on your expected output, seems like just change the table visual to the martix visual can do that instead of ranking anything. Or I have misunderstood it.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl 

About question 1: the problem is that the Business Units calculations are not correct. A sales of 7300 in 2021 and 6200 in 2020 is a growth of 17.7% instead of -48.23%. The totals are correct. But I want to have the business units correct as well.

Draging the column spacing is a great solution! Thanks for that!

About question 2: I made it as a matrix the way you did as well, but then I can rank on sales in 2021. I want to have item A1234 on the highest line (6400 sales), then A5678 on the second line (4900 sales) and A1357 on the third line (3400 sales).

Is this possible?

Thanks a lot!

Maartje

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Sorry for that I have ignored each BU growth%, change the first formula from all() to allexcept() to get the right result:

growth% = 
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLEXCEPT(  'Table' ,'Table'[Business]),
            'Table'[Fiscal Year]
                = CALCULATE (
                    MAX ( 'Table'[Fiscal Year] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Year] < MAX ( 'Table'[Fiscal Year] ) )
                )
        )
    )
VAR _sales =
    SUM ( 'Table'[Sales] )
VAR _presales =
    IF ( ISBLANK ( _previous ), SUM ( 'Table'[Sales] ), _previous )
RETURN
    DIVIDE ( _sales - _presales, _presales, 0 )

For the ranking issue, you can create a measure like this, put it in the matrix and sort by this measure:

Salessum = 
IF(
    HASONEVALUE('Table'[Fiscal Year]),SUM('Table'[Sales]),
    CALCULATE(SUM('Table'[Sales]),'Table'[Fiscal Year] = 2021)
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl 

The second part of my problem is solved! I can sort on 2021 now so that is great!!

The first part, the index, is working in my test file (the one I have shown in the topic) but if I apply it to my real dataset (which contains a lot of sensitive information so I can't share it), the index is not correct. I get these numbers:

Eventhough we are growing in 2021 vs 2020 is shows a (big) negative index. Any idea how this is possible? I copied the formula from the test file and only adjusted the name of the table but for the rest everything is the same.

I hope you have any idea because then my dashboard will be complete;-).

Many thanks!

Maartje

[amitchandak]

@Anonymous , Create a date from week

Week Date = var _st = date([year],1,1)
var _week = [Week] *1
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1

You can use time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))