Row and Columns

Question

Hi AllIm fairly new here -Need attrition&nbsp;Name&nbsp; &nbsp; &nbsp; &nbsp;2016&nbsp; &nbsp; &nbsp; &nbsp;2017&nbsp; &nbsp; &nbsp; 2018&nbsp; &nbsp; &nbsp;2019&nbsp;&nbsp;David&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A4Mike&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -LOLO&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A3ETC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A1what I want to see compare attriton rate between 2017 to 2016, 2018 to 2017&nbsp; &nbsp;and 2019 to 2018if 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&nbsp;

v-xicai · Answer

Hi udurrani ,&nbsp;According to your description, I create sample data to test the scenario. You can implement your demand following steps below.&nbsp;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.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Then, create measure named Attriton rate to get the attriton rate between years.&nbsp;Attriton rate = VAR _previous = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1), 'Table1'[Name]=MAX(Table1[Name])&amp;&amp;Table1[Year] = MAX(Table1[Year]) -1))VAR _current = CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1),'Table1'[Name]=MAX(Table1[Name])&amp;&amp;Table1[Year] =MAX(Table1[Year]))) returnIF(_previous&lt;&gt;BLANK(),DIVIDE(_current-_previous,_previous,0),BLANK())&nbsp;Choose the table visual to display the result.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWL26RFMgEJFm858qtN-b-sBkzdi7_wDpLrZ-Hd1wS7v-g?e=S3cOMG&nbsp;Best Regards,Amy &nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

Hi, thank you for your responsei dont understand value column - and why they have different number-for example (see diagram below) on the matrix reporti got "ben" appearing all 4 years. therefore&nbsp; he would have 0 attritionmeanwhile "jon"&nbsp; appears 2016 and 2017 not in 2018 ,&nbsp; therefore add&nbsp;1 in the 2018 columnName2016201720182019bena1a2a3a4jona2a2&nbsp;&nbsp;mikea2a2a3&nbsp;petera1a1&nbsp;&nbsp;rona2a2a3&nbsp;pottera2a3&nbsp;&nbsp;harrya2a3a4&nbsp;ideally i like to have where i can say 'total attrition for 2018 year is 40, whereas 2017 59

anonymous · Answer

Hi its not workingso i got this nowName&nbsp;&nbsp;&nbsp;&nbsp; 2016&nbsp;&nbsp; 2017&nbsp;&nbsp; 2018&nbsp; 2019&nbsp;&nbsp;&nbsp;&nbsp; totalBob&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0Mike&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0tom&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0jerry&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0etc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0* i know BOB started the comp in 2016 and still in 2019Mike started 2016 - but has left (blank)Tom left at 2018 (blank)***i want to summaries by years - like countif/sumiffor example 2017 attrition is (it needs to add the blank as 1)

Forum Discussion

Row and Columns

3 Replies

Related content

Column to Row

Grouping rows by Column and then updating Column

Column to Row

Apply data to all rows in a filtered column

Add duplicate row based on 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