Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello again community. How are you?
Thank all that review my posts and have been helped.
Now I need to know why I have this scenario. Maybe I need to complement my Measure with some trick or change it completely.
Objective: Show YOY Growth percent without "Hundred part", just the real Increase or decrease percent.
Measures I am using:
Prev Year Billing:=CALCULATE(SUM(MiddleOffice16_17[VALUE]);SAMEPERIODLASTYEAR(MiddleOffice16_17[MONTH_CORRECTED]))
YOY Billing Growth:=CALCULATE(SUM(MiddleOffice16_17[VALUE]))-[Prev Year Billing]
YOY Billing Perc Growth:=DIVIDE([YOY Billing Growth];[Prev Year Billing];0)
Correct Calculation, but WRONG visualization:
BILLING MO 2016 | BILLING MO 2017 | YOY Billing Perc Growth |
28.424.660 | 32.624.662 |
Obs: I want to show just 14,78% which represents the increase.
Wrong Calculation Ex 1:
CLIENT | BILLING MO 2016 | BILLING MO 2017 | YOY Billing Perc Growth |
DECATHLON | 37.972 | 37.972 | 100,00% |
Obs: The values are the same. So I did not increase.
Wrong Calculation Ex 2:
BILLING MO 2016 | BILLING MO 2017 | YOY Billing Perc Growth |
4.200.000 | 0,00% |
Obs: Increase is 100%, but I cannot show this.
Thank you so much again.
Solved! Go to Solution.
Hi try the following as YOY% measure
YOY% = Calculate (If(isblank([Prev Year Billing]),[SUM(MiddleOffice16_17[VALUE])]/[SUM(MiddleOffice16_17[VALUE])],([SUM(MiddleOffice16_17[VALUE])]-[Prev Year Billing])/[Prev Year Billing]) )
And define this as type Percentage in the Modelling tab.
If this solves your issue please accpet this as Solution and also give KUDOS.
Cheers
CheenuSing
This is perfect. You can use the IFerror function to take care of exceptions.
CheenuSing
If you have made the YOY%error as data type and have used the formula (Current Year - Previous Year) / (current Year) you will always the incremental percentageonly and not 100 + incremental percentage.
This happens when your formula is [CurrentYear] / [PreviousYear]
Hope this clarifies
CheenuSing
YOY% = Calculate (If(isblank([Prev Year Billing]),[SUM(MiddleOffice16_17[VALUE])]/[SUM(MiddleOffice16_17[VALUE])],([SUM(MiddleOffice16_17[VALUE])]-[Prev Year Billing])/[Prev Year Billing]) )
Hi try the following as YOY% measure
YOY% = Calculate (If(isblank([Prev Year Billing]),[SUM(MiddleOffice16_17[VALUE])]/[SUM(MiddleOffice16_17[VALUE])],([SUM(MiddleOffice16_17[VALUE])]-[Prev Year Billing])/[Prev Year Billing]) )
And define this as type Percentage in the Modelling tab.
If this solves your issue please accpet this as Solution and also give KUDOS.
Cheers
CheenuSing
Hello @CheenuSing, How are you?
First, thank you so much for your fast reply. I could see your suggestion just now (Sunday, Oct, 09 - 2:37pm Brazil time).
I think we are almost there with your DAX formula. I just got one error in one line of Pivot Table and I complemented the formula like bellow (I do not know if this is the best approach to do so).
Actual Calculate Field:
YOY%2:=CALCULATE(IFERROR(IF(ISBLANK([Prev Year Billing]);SUM(MiddleOffice16_17[VALUE])/SUM(MiddleOffice16_17[VALUE]);(SUM(MiddleOffice16_17[VALUE])-[Prev Year Billing])/[Prev Year Billing]);0))
Why I am using IFERROR function? Because of this:
SPECIALTY | CLIENT | BILLING MO 2016 | BILLING MO 2017 | YOY% Error |
ECSELIS | 0 | #NÚM! | ||
NEW BUSINESS | 0 | #NÚM! | ||
Total Geral | 0 | #NÚM! |
Please check if my approach is the best and if you can suggest another better way. If my complement is right, I understand we can set this case as resolved, right?
Thank you again.
This is perfect. You can use the IFerror function to take care of exceptions.
CheenuSing
I forgot to mention about showing just the real part of increase (15,8%) and not 115,8%. Do you know how?
And taking a further analysis, I have as bellow using actual calculated field. This is not correct, right? Because in this case I understand we did not increase. I will consider an increase of X% when 2017 Billing is higher than 2016 Billing.
BILLING MO 2016 | BILLING MO 2017 | YOY% Error |
164.624 | 164.624 | 100,0% |
If you have made the YOY%error as data type and have used the formula (Current Year - Previous Year) / (current Year) you will always the incremental percentageonly and not 100 + incremental percentage.
This happens when your formula is [CurrentYear] / [PreviousYear]
Hope this clarifies
CheenuSing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |