AVERAGEIFS in Power BI using Measures

[Multiverse_76]

Hi

In the below Matrix, each of the Columns is a Measure.  CY = Current Year and PY = Previous Year.  What I want to do is find the Average Salary change for each Grade, but only if the Salary Change is between 0 and 100% and only if there are values in the Grade Column. In excel I achieve this with:

AVERAGEIFS( Salary Change, Salary Change >=0, Salary Change <1, CY 6000 - Fee Earners LLP >0, PY 6000 - Fee Earners LLP >0)

Could you offer some guidance on how I might achieve this in Power BI?

Many thanks!

[amitchandak]

You can use AVERAGEX

AVERAGEX(Table, <Use if to choose measures>)

table can be filter(table, filter)

https://docs.microsoft.com/en-us/dax/averagex-function-dax

https://community.powerbi.com/t5/Desktop/averagex/td-p/150294

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Multiverse_76]

Many thanks.  I'm trying but not having much success...

Average Increase = CALCULATE(AVERAGEX(filter('Combined_Tbl',[Salary Change]>0 && [Salary Change] <1 && 'Combined_Tbl'[CY 6000 - Fee Earners LLP] >0 && 'Combined_Tbl'[PY 6000 - Fee Earners LLP] >0 ) , [Salary Change]))

[amitchandak]

[Salary Change] is a measure? From what level you want to group the data

[Multiverse_76]

@amitchandak  thanks ever os much for your help.  Yes, [Salary Change] is a Measure.  All of the Columns in the table above are Measures with the exception of Staff Name which I had to keep off for obvious reasons!

I want to get to this:

[amitchandak]

Review your salary change condition. If it is fine try like

Average Increase = CALCULATE(AVERAGEX(
	filter(summarize('Combined_Tbl','Combined_Tbl'[Nominal Desc],"_sch",sum([Salary Change]),"_CY",sum('Combined_Tbl'[CY 6000 - Fee Earners LLP])
	, "_PY",sum('Combined_Tbl'[PY 6000 - Fee Earners LLP])),[_sch]>0 && [_sch] <1 && [_CY] >0 && [_PY 6000] >0 ) , [_sch))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Multiverse_76]

Hi @amitchandak & Community

Apologies for the radio silence.  I went back to the drawing board on my model to see if it would help overcome some issues.  It's a shame I can't share my pbix but I have mocked up an example in Excel.

Excel file

The things I am really struggling with (about 2 weeks), is how to achieve these items in the excel file, in Power BI:

• Year on Year Increase
• Share Profit
• Average Increase
• Weighted Average

The formulas are at the bottom of the sheet.  

Context: The left hand-side of the matrix is Curreny Year, the right-side Prior Year.  Its tracking whether employees leave, get promoted, are new starters.  

The other thing I'm struggling with is filtering a measure.  I'm using a measure to work out which category each employee belongs to and I'd like to filter to just leavers or new starters etc.  I think I need to use a disconnected table but so far I haven't succeeded.

Any guidance would be greatly appreciated.

[Multiverse_76]

Can anyone help?