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 all, good afternoon!
I just registered myself in this community. I am a newbie in DAX, but trying to apply as much as possible some daily financial situations I already have. "I think when you have an enviroment at your disposal, this is the best way to put in practice what you are learning and advance your abilities."
So, I am trying to create a measure (according to "Power Pivot and Power BI" book this is the best way and I am trying to avoid Column calculation approach) that will compare all investment BY [PRODUCTS] from 2015 and 2016 in % (both increase and decrease % numbers).
Table Name = 'PILOT';
Column 'PILOT'[YEAR] = 2015 & 2016 values;
Column 'PILOT'[INVESTMENT AMT] = Amounts in BRL;
Column 'PILOT'[PRODUCTS] = All products
Objective: Compare 2015 and 2016 in % to show in a Pivot Table (Power Pivot) increase and decrease scenarios;
Problem found: There are some products that does not have any investment on 2015 and does in 2016. So, the formula has to be dynamically enough to recognize this and show 100% increase and vise versa (-100%).
I do not know how to accomplish this result using DAX in calculated field (Measure). I have tried DIVIDE function, but failed.
Thank you all in advance,
Jaderson Almeida (Jimmy)
Solved! Go to Solution.
Yeah, of course @bajimmy1983! I just created these three dax calculations:
2015 = CALCULATE(SUM(PILOT[INVESTMENT AMT]);PILOT[YEAR]=2015)
2016 = CALCULATE(SUM(PILOT[INVESTMENT AMT]);PILOT[YEAR]=2016)
YOY(%) = IF(OR(ISBLANK([2015])=TRUE();ISBLANK([2016])=TRUE());BLANK();DIVIDE([2016];[2015])-1) - format percentage with one decimal place
Is it clear?
You are welcome Jimmy.
Regards.
Pavel
Yeah, of course @bajimmy1983! I just created these three dax calculations:
2015 = CALCULATE(SUM(PILOT[INVESTMENT AMT]);PILOT[YEAR]=2015)
2016 = CALCULATE(SUM(PILOT[INVESTMENT AMT]);PILOT[YEAR]=2016)
YOY(%) = IF(OR(ISBLANK([2015])=TRUE();ISBLANK([2016])=TRUE());BLANK();DIVIDE([2016];[2015])-1) - format percentage with one decimal place
Is it clear?
You are welcome Jimmy.
Regards.
Pavel
Is there no embedded formula which could help obtain the same result without having to type the DAX measure. Its Microsoft i am sure you guys can create a code which will help formulate that automatically.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |