Percentage YOY calculation based on columns available in Table

[ramshoney1225]

HI I have to calcualte % values for each year with the combination of Company available in a Pivot table and that measure should calcuate dynamically when i pull in Pivot table.

Ex: for year 2019 ( apple ) =  (Apple 2019 Headcount ) - (Apple 2018 Headcount) / (Apple 2018 Headcount )

                                           =  ( 106700 - 105800 ) / 105800       --> Nearly 1% 

                   2018 ( apple ) =  (Apple 2018 Headcount ) - (Apple 2017 Headcount) / (Apple 2017 Headcount )

                                          =  (105800 - 106400) / 106400          --> Nearly  -1%

                  2017 ( apple ) =  (Apple 2017 Headcount ) - (Apple 2016 Headcount) / (Apple 2016 Headcount )

                   2016 ( apple) = ( Should be blank of not having 2015 Head count data ) 

Simillary, same calucation should happend for NOkia, Samsung.

How to do that dynamic calcuation with only one measure i need to create ?

Actaully i'm unabl to upload the PBIX file, didnt see option to upload but the data is this only .

Company	Year	Headcount
Apple	2019	106700
Apple	2018	105800
Apple	2017	106400
Apple	2016	102500
Samsung	2019	92005
Samsung	2018	103564
Samsung	2017	105870
Samsung	2016	104553
Nokia	2019	146768
Nokia	2018	150711
Nokia	2017	152000
Nokia	2016	155000

THanks,

Rams

[nandukrishnavs · Accepted solution]

@ramshoney1225 

Try this DAX measure

Percentage YOY =
VAR _currentYear =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _company =
    SELECTEDVALUE ( 'Table'[Company] )
VAR _current =
    SUM ( 'Table'[Headcount] )
VAR _prior =
    SUMX (
        FILTER (
            ALL (
                'Table'[Company],
                'Table'[Headcount],
                'Table'[Year]
            ),
            ( 'Table'[Year] = _currentYear - 1 )
                && ( 'Table'[Company] = _company )
        ),
        'Table'[Headcount]
    )
VAR _yoy =
    DIVIDE (
        _current - _prior,
        _prior,
        BLANK ()
    )
RETURN
    _yoy

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂