Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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:
Solved! Go to Solution.
hi, @Anno2019
Just try this way as below:
Step1:
Create a type dim table
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:
and here is pbix file, please try it.
Best Regards,
Lin
Hi,
When does your FY start? It will be ideal if you can share the link from where i can download your PBI file.
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.
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
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
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:
and here is pbix file, please try it.
Best Regards,
Lin
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |