Row and Columns

[Anonymous]

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 

[v-xicai]

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.

[Anonymous]

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

[Anonymous]

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)