cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Dax - create a calculated column referencing a measure

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    

1 ACCEPTED SOLUTION
technolog
Super User
Super User

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".

 

View solution in original post

2 REPLIES 2
technolog
Super User
Super User

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".

 

Anonymous
Not applicable

More data/description needed. Would you mind placing a sample file on a shared drive and paste a link to it, please?

Best
D

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors