Forum Discussion
Helper IVAverage Annual Percent Change
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:
Microsoft EmployeeHi 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
AlejandroPCarOne 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.
- Ashish_Mathur
Super User
Hi,
So in the second Table, you would like to see average annual percentage in the Total column. Am i correct?
- AlejandroPCarNot necessarily but could be helpful thanks