Percentage YOY calculation based on columns available in Table

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

Need Help

nandukrishnavs

6 years ago

Anonymous

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 🙂

Anonymous
6 years ago
Hi Anonymous ,

You can use the below measure

Previous Year Headcount % =
Var _max = MAX ('Table'[Year])
Var _previousYear = CALCULATE( MAX('Table'[Year]), FILTER(ALL('Table'), 'Table'[Year] < _max && 'Table'[Company] = MAX('Table'[Company])))
Var _previousCompany = CALCULATE( MAX('Table'[Company]), FILTER(ALL('Table'), 'Table'[Year] < _max && 'Table'[Company] = MAX('Table'[Company])))
var _previousYearHeadcount = CALCULATE( MAX('Table'[Headcount]), FILTER(ALL('Table'), 'Table'[Year] = _previousYear && 'Table'[Company] = _previousCompany))

RETURN

DIVIDE(MAX('Table'[Headcount]) - _previousYearHeadcount,_previousYearHeadcount)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

10 Replies

nandukrishnavs
Community Champion
6 years ago
Anonymous

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 🙂
- Anonymous
  Not applicable
  6 years ago
  Hi nandukrishnavs,,amitchandak ,Anonymous
  
  Thanks for the promt Help with multiple measures :), But in my case all are coming from one table so i tried what Nandu mentioned, but i'm getting nulls, may be i missed complete information what i need.
  
  Below is the once which i have showing the screenshot,but i created in Excle, which i'm uanble to write in DAX
  Which i want the same as below in Power BI
  
  One with actual values table, and one with % values table ( not having any Actual values )
  
  😞 this is what my second graph should show lik, but i created that in excel,
  In my case i want that should be written in Dax 😞
  
  Thanks,
  Rams
  - nandukrishnavs
    Community Champion
    6 years ago
    Anonymous You have to create a DAX measure. I think you have created a calculated column.
    I tried your sample data, it is working for me. You can find the snapshot in my original post.
    
    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos 🙂
Anonymous
Not applicable
6 years ago
Hi Anonymous ,

You can use the below measure

Previous Year Headcount % =
Var _max = MAX ('Table'[Year])
Var _previousYear = CALCULATE( MAX('Table'[Year]), FILTER(ALL('Table'), 'Table'[Year] < _max && 'Table'[Company] = MAX('Table'[Company])))
Var _previousCompany = CALCULATE( MAX('Table'[Company]), FILTER(ALL('Table'), 'Table'[Year] < _max && 'Table'[Company] = MAX('Table'[Company])))
var _previousYearHeadcount = CALCULATE( MAX('Table'[Headcount]), FILTER(ALL('Table'), 'Table'[Year] = _previousYear && 'Table'[Company] = _previousCompany))

RETURN

DIVIDE(MAX('Table'[Headcount]) - _previousYearHeadcount,_previousYearHeadcount)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
- Anonymous
  Not applicable
  6 years ago
  Hi Anonymous
  
  I tried with this as well, but getting all 0.50 Valus 😞
  - Anonymous
    Not applicable
    6 years ago
    Anonymous ,
    
    Please create a measure not a Calculated Column.
    
    Regards,
    Harsh Nathani

amitchandak

Super User

6 years ago

Anonymous , if you do not have date, then make sure you year is in separate table and try

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year] -[Last Year]
Diff % = divide( [This Year] -[Last Year],[Last Year])
//if you have date with date calendar

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))


YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

Forum Discussion

Percentage YOY calculation based on columns available in Table

10 Replies

Related content

Calculate Percentage

Calculating Percentages

Projected Available Balance Dax Calculation Help

ParaTimeLine not available

Calculate percentage