Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
I have a measure (Change %) giving me the % decrease/increase by year from a series of values in the Production table.
I want to calculate the average decline but only when the Change % is positive.
I think I need to create a Yes/No flag in the Production table to toddle on/off for this to work but I'm open to options.
Thanks
Solved! Go to Solution.
I understand you're trying to calculate the average decline but only for the years when the Change % is positive. You're right that one way to approach this is by creating a calculated column to flag the rows where the Change % is positive.
First, you'd create a calculated column in the Production table. Let's call it "PositiveChangeFlag". The formula for this column would be something like:
PositiveChangeFlag = IF([Change %] > 0, "Yes", "No")
This will give you a "Yes" for rows where the Change % is positive and "No" otherwise.
Now, to calculate the average decline for only those years with a positive Change %, you'd create a new measure. Let's call it "Avg Positive Change". The formula would be something like:
Avg Positive Change = CALCULATE(AVERAGE(Production[Change %]), Production[PositiveChangeFlag] = "Yes")
This measure will give you the average of the Change % but only for the rows where the PositiveChangeFlag is "Yes".
I understand you're trying to calculate the average decline but only for the years when the Change % is positive. You're right that one way to approach this is by creating a calculated column to flag the rows where the Change % is positive.
First, you'd create a calculated column in the Production table. Let's call it "PositiveChangeFlag". The formula for this column would be something like:
PositiveChangeFlag = IF([Change %] > 0, "Yes", "No")
This will give you a "Yes" for rows where the Change % is positive and "No" otherwise.
Now, to calculate the average decline for only those years with a positive Change %, you'd create a new measure. Let's call it "Avg Positive Change". The formula would be something like:
Avg Positive Change = CALCULATE(AVERAGE(Production[Change %]), Production[PositiveChangeFlag] = "Yes")
This measure will give you the average of the Change % but only for the rows where the PositiveChangeFlag is "Yes".
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.