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 All
Im fairly new here -
Need attrition
Name 2016 2017 2018 2019
David A1 A2 A3 A4
Mike A1 A2 - -
LOLO A1 A2 A3
ETC A1
what I want to see compare attriton rate between 2017 to 2016, 2018 to 2017 and 2019 to 2018
if this was excel i woud have done index match - to get the answer i want - (columns would be year dates - and following up with if ststement)
but power BI - is another story
Hi udurrani ,
According to your description, I create sample data to test the scenario. You can implement your demand following steps below.
Firstly, convert the column 2016-2019 to row data like picture below. Named the new column "Year", and change its data type to Whole Number.
Then, create measure named Attriton rate to get the attriton rate between years.
Attriton rate =
VAR _previous = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1), 'Table1'[Name]=MAX(Table1[Name])&&Table1[Year] = MAX(Table1[Year]) -1))
VAR _current = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1),'Table1'[Name]=MAX(Table1[Name])&&Table1[Year] =MAX(Table1[Year])))
return
IF(_previous<>BLANK(),DIVIDE(_current-_previous,_previous,0),BLANK())
Choose the table visual to display the result.
Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWL26RFMgEJFm858qt...
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, thank you for your response
i dont understand value column - and why they have different number
-for example (see diagram below) on the matrix report
i got "ben" appearing all 4 years. therefore he would have 0 attrition
meanwhile "jon" appears 2016 and 2017 not in 2018 , therefore add 1 in the 2018 column
Name | 2016 | 2017 | 2018 | 2019 |
ben | a1 | a2 | a3 | a4 |
jon | a2 | a2 | ||
mike | a2 | a2 | a3 | |
peter | a1 | a1 | ||
ron | a2 | a2 | a3 | |
potter | a2 | a3 | ||
harry | a2 | a3 | a4 |
ideally i like to have where i can say 'total attrition for 2018 year is 40, whereas 2017 59
Hi its not working
so i got this now
Name 2016 2017 2018 2019 total
Bob 0 0 0 0 0
Mike 0
tom 0 0 0
jerry 0 0
etc 0 0 0 0
* i know BOB started the comp in 2016 and still in 2019
Mike started 2016 - but has left (blank)
Tom left at 2018 (blank)
***i want to summaries by years - like countif/sumif
for example 2017 attrition is (it needs to add the blank as 1)
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |