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.
Hi!
I need some help here. How can I do a measure of Average Annual Percent Change? I mean, I have a table like this one:
For example, the Average Annual Percent Change for the first row (1 INVERSIONES RAM LTDA) will be =( 0,0% + -77,99% - 47,56%) / 3 (3 years selected) = -41,85%. And then, if I need to select more years the measure also calculate it.
Thanks a lot!
Solved! Go to Solution.
Hi,
This is what i modified your "Variación % Anual ER" measure to
= if(HASONEVALUE(TiempoA[año]),DIVIDE([Variación Anual ER], [Estado Resultados I - 1], 0),AVERAGEX(VALUES(TiempoA[año]),DIVIDE([Variación Anual ER], [Estado Resultados I - 1], BLANK())))
See the Total column in the image below:
Hi @AlejandroPCar,
@Ashish_Mathur's solution is perfect. If you have any question, please post here.
Best Regards!
Dale
Hi @AlejandroPCar,
What's your the structure of your model? What's the formula of Annual Percent Change? Here is a sample. You can reference.
One product table 'Product', one date table 'Date', one sales data table 'Sales'.
The Annual Percent Change here is:
Measure 16 = VAR QuantityLastYear = CALCULATE ( SUM ( Sales[Quantity] ), PREVIOUSYEAR ( 'Date'[Date] ) ) VAR QuantityThisYear = SUM ( Sales[Quantity] ) RETURN DIVIDE ( QuantityThisYear - QuantityLastYear, QuantityLastYear, 0 )
Then the Average Annual Percent Change is:
Measure 17 = AVERAGEX ( SUMMARIZE ( 'Sales', 'Product'[Color], 'Date'[Date].[Year], "Percentage", VAR QuantityThisYear = SUM ( Sales[Quantity] ) VAR QuantityLastYear = CALCULATE ( SUM ( Sales[Quantity] ), PREVIOUSYEAR ( 'Date'[Date] ), 'Product'[Color] = EARLIER ( 'Product'[Color] ), ALL ( Sales ) ) RETURN DIVIDE ( QuantityThisYear - QuantityLastYear, QuantityLastYear, 0 ) ), [Percentage] )
A little complicated. If you provide a sample, I can help you. The PBIX file is great. A sample in text mode is also OK.
Best Regards!
Dale
One question, why the total of your example does not work like average result in the selected years? Here is my pbix file: https://1drv.ms/u/s!AtTnrgPUQzQCjCb_Ut1ydMbZyJbk Thank you a lot for help me.
Hi,
So in the second Table, you would like to see average annual percentage in the Total column. Am i correct?
Then what exact result are you expecting?
Hi,
This is what i modified your "Variación % Anual ER" measure to
= if(HASONEVALUE(TiempoA[año]),DIVIDE([Variación Anual ER], [Estado Resultados I - 1], 0),AVERAGEX(VALUES(TiempoA[año]),DIVIDE([Variación Anual ER], [Estado Resultados I - 1], BLANK())))
See the Total column in the image below:
Thank you very much , it works very well.
You are welcome.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |