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!
I am needing help on something that is probably obvious. Within a matrix, the total column - I would like to show the AVG change of all crop years. Since 2017 is the first year, there would be no change value displayed - and excluded from the average
File link https://1drv.ms/u/s!AmBVCme14p7xszd7fzk4r4eDkxlp?e=EuJeIx
MeasureName = YOY TEST
PS - what I really want to get to is % Change YOY, but need to get this initial step in place first.
YOYTest =
Var LastYr = min(ProjectSetup[Crop Year])-1
VAR LastYrValue = CALCULATE([IncomeStatement/Crop Acre],ProjectSetup[Crop Year]=LastYr)
VAR ThisYrValue = [IncomeStatement/Crop Acre]
VAR IfZero = OR( ThisYrValue = 0, LastYrValue = 0 )
vAR AnnualChange = if(IfZero,BLANK(),ThisYrValue-LastYrValue)
//vAR AnnualChange = ThisYrValue-LastYrValue
Var ValueAvgX = AVERAGEX(
FILTER(values(ProjectSetup[Crop Year]),ProjectSetup[Crop Year]>min(ProjectSetup[Crop Year])),
//VALUES( 'ProjectSetup'[Crop Year] ),
Var LastYr = min(ProjectSetup[Crop Year])-1
VAR LastYrAvgXValue =
CALCULATE(
[IncomeStatement/Crop Acre],
ProjectSetup[Crop Year]=LastYr)
VAR ThisYrAvgXValue = [IncomeStatement/Crop Acre]
VAR IfZero = OR( ThisYrValue = 1, LastYrValue = 0 )
vAR AvgXChange = if(IfZero,BLANK(),ThisYrValue-LastYrValue)
Return
//AvgXChange)
ThisYrAvgXValue-LastYrAvgXValue)
RETurn
//ValueAvgX
if(HASONEVALUE(ProjectSetup[Crop Year]),AnnualChange,ValueAvgX)
Solved! Go to Solution.
Yes! Thank you... I knew it would be a simple solution. ugh! I think both answers ended up doing the same thing. But if I may ask a followup question? Is it possible to make this all work within a single measure? When I am just taking hte average over years - I have a nice one measure solution. Having introducing "YOY change - that seemed to complicate?
I realize we would be nesting variables, etc inside an AvgX - but trying to keep my models from being cluttered with extra measures.
The value circled in blue below is an example of single vs Avg calculations within a single measure.
Thank you again!
Hi,
This measure works
Measure = if(HASONEVALUE(ProjectSetup[Crop Year]),[YOYTest],AVERAGEX(VALUES(ProjectSetup[Crop Year]),[YOYTest]))
Hope this helps.
Yes! Thank you... I knew it would be a simple solution. ugh! I think both answers ended up doing the same thing. But if I may ask a followup question? Is it possible to make this all work within a single measure? When I am just taking hte average over years - I have a nice one measure solution. Having introducing "YOY change - that seemed to complicate?
I realize we would be nesting variables, etc inside an AvgX - but trying to keep my models from being cluttered with extra measures.
The value circled in blue below is an example of single vs Avg calculations within a single measure.
Thank you again!
Hi,
If i try, i probably can but i would not want to do so just to keep the measures short and easy to understand. If my previous reply helped, please mark it as Answer.
Hello - my intention with this measure is to use in conditional formatting. I have alot of additional pieces worked out - and what you are seeing is just the problematic part. I hope to insert this larger, single measure inside a KPI trend indicator. Thanks!
Hi,
Even if you have a chain of measures and the last measure gives you the correct measure, then apply conditioal formatting with the last measure. You will not face a problem.
Hi @Dellis81 ,
Final get is this:
Do the below steps:
Step 1,create new measure like below:
YOYTest1 =
VAR LastYr =
MIN ( ProjectSetup[Crop Year] ) - 1 //GET 2016
VAR LastYrValue =
CALCULATE ( [IncomeStatement/Crop Acre], ProjectSetup[Crop Year] = LastYr )
VAR ThisYrValue = [IncomeStatement/Crop Acre]
VAR IfZero =
OR ( ThisYrValue = 0, LastYrValue = 0 )
VAR AnnualChange =
IF ( IfZero, BLANK (), ThisYrValue - LastYrValue )
VAR test1 =
AVERAGEX ( VALUES ( 'ProjectSetup'[Crop Year] ), AnnualChange )
RETURN
AnnualChange
Step 2,then base on the measure create a new measure:
Measure = AVERAGEX(VALUES('ProjectSetup'[Crop Year]),[YOYTest1])
Step ,change the base measure YOYTEST to the below:
YOYTest =
Var LastYr = min(ProjectSetup[Crop Year])-1
VAR LastYrValue = CALCULATE([IncomeStatement/Crop Acre],ProjectSetup[Crop Year]=LastYr)
VAR ThisYrValue = [IncomeStatement/Crop Acre]
VAR IfZero = OR( ThisYrValue = 0, LastYrValue = 0 )
vAR AnnualChange = if(IfZero,BLANK(),ThisYrValue-LastYrValue)
//vAR AnnualChange = ThisYrValue-LastYrValue
RETurn
//ValueAvgX
if(HASONEVALUE(ProjectSetup[Crop Year]),AnnualChange,_MeasureTable[Measure])
Wish it is helpful for you!
Best Regards
Lucien
Yes, apologize for lack of clarity. Thank you!
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |