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.
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!
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
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]))
[Salary Change] is a measure? From what level you want to group the data
@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:
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
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.
The things I am really struggling with (about 2 weeks), is how to achieve these items in the excel file, in Power BI:
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.
Can anyone help?
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 |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
88 | |
66 |