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
Anno2019
Helper IV
Helper IV

Customer Trending - How to calculate New, Lost, growth & degrowth

Hi Guys

I need a Power BI guru to help me...

I am struggling with formula's to show a customers trend "Lost, New, Growth, Degrowth" based on customer performance YTD vs PYTD.

In excel it looks like this - I need to calculate the Columns YTD 2018, YTD 2019, Var, % and finally the Trend.  Ultimately, this must be at a Customer Name Level as in the excel example.

My data is at a shipment level.

Excel Example.PNG

 

In Excel, the formula for Trend Column would look like this:

IF(AND([YTD 2018]=0, [YTD 2019]=0),"No Business",

IF([YTD 2019]=[Var],"New",

IF(AND([YTD 2018]<0, [YTD 2019]=0),"Lost",

IF(AND([YTD 2018]>0, [YTD 2019]=0),"Lost",

IF([Var]>0,"Growth",

IF(AND([Var]<>-1, [Var]<0),"De Growth",

IF(AND([%]<>-100%,[Var]<0),"De Growth",

IF([Var]=0,"No Change"))))))))

 

 

My Power BI Data looks like this:

PBI Data list.PNG

 

 

 

1 ACCEPTED SOLUTION

hi, @Anno2019 

Just try this way as below:

Step1:

Create a type dim table

1.JPG

Step2:

Create a measure

Measure = SWITCH(SELECTEDVALUE('Table'[Type]),
"De Growth",SUMX(FILTER(VALUES('DATA Sheet'[Customer Name]),[Trend]="De Growth"),[Var]),
"Growth",SUMX(FILTER(VALUES('DATA Sheet'[Customer Name]),[Trend]="Growth"),[Var]),
"Lost",SUMX(FILTER(VALUES('DATA Sheet'[Customer Name]),[Trend]="Lost"),[Var]),
"New",SUMX(FILTER(VALUES('DATA Sheet'[Customer Name]),[Trend]="New"),[Var]))

Step3:

Then drag type field and this measure into a visual

Result:

2.JPG

and here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
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

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

When does your FY start?  It will be ideal if you can share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 Ashish, i just posted the link together with my reply to Lin.  My FY runs from Jan to Dec

Hi,

Mine would be a measure based solution and from your previous reply i guess that would not meet your requirements.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-lili6-msft
Community Support
Community Support

hi, @Anno2019 

You need to try this way:

Create four new measures by TOTALYTD Function

for example:

YTD 2018 = CALCULATE(TOTALYTD(SUM(Table1[Qty]),Table1[Date]),Table1[Year]=2018)
YTD 2019 = CALCULATE(TOTALYTD(SUM(Table1[Qty]),Table1[Date]),Table1[Year]=2019)
Var = [YTD 2019]-[YTD 2018]
% = DIVIDE([Var],[YTD 2018])

Then create the Trend measure by the same logic

Trend =
IF (
    AND ( [YTD 2018] = 0, [YTD 2019] = 0 ),
    "No Business",
    IF (
        [YTD 2019] = [Var],
        "New",
        IF (
            AND ( [YTD 2018] < 0, [YTD 2019] = 0 ),
            "Lost",
            IF (
                AND ( [YTD 2018] > 0, [YTD 2019] = 0 ),
                "Lost",
                IF (
                    [Var] > 0,
                    "Growth",
                    IF (
                        AND ( [Var] <> -1, [Var] < 0 ),
                        "De Growth",
                        IF ( AND ( [%] <> -1, [Var] < 0 ), "De Growth", IF ( [Var] = 0, "No Change" ) )
                    )
                )
            )
        )
    )
)

Best Regards,

Lin

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

Hi Lin

Thank you for the quick response, really appreciate it.  it is working...

The problem is that I cannot use a Measure as a filter or use it as a card.

I need to show the total [Var] Total for each trend as a card... and in another analysis, I would need to use the Trend as a filter, would be nice to use them as Legends in a graph.

Any idea how to achieve this?  To have this trend as a dimension?  as to use it as a filter or card?

 

https://www.dropbox.com/s/im7xa2p9kl6feb5/Excel%20File_how%20it%20should%20look.xlsb?dl=0

 

https://www.dropbox.com/s/3j7dhxs2nzjkafp/TEST%20File.pbix?dl=0

hi, @Anno2019 

Just try this way as below:

Step1:

Create a type dim table

1.JPG

Step2:

Create a measure

Measure = SWITCH(SELECTEDVALUE('Table'[Type]),
"De Growth",SUMX(FILTER(VALUES('DATA Sheet'[Customer Name]),[Trend]="De Growth"),[Var]),
"Growth",SUMX(FILTER(VALUES('DATA Sheet'[Customer Name]),[Trend]="Growth"),[Var]),
"Lost",SUMX(FILTER(VALUES('DATA Sheet'[Customer Name]),[Trend]="Lost"),[Var]),
"New",SUMX(FILTER(VALUES('DATA Sheet'[Customer Name]),[Trend]="New"),[Var]))

Step3:

Then drag type field and this measure into a visual

Result:

2.JPG

and here is pbix file, please try it.

 

Best Regards,

Lin

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

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.