Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 🙂
Hi @ramshoney1225 ,
You can use the below measure
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@ramshoney1225 , 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-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.
Hi @ramshoney1225 ,
You can use the below measure
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @harshnathani , @nandukrishnavs
Thanks for the help, the both measures works as expected shared by you 🙂
As intial i got response from @nandukrishnavs , will make as "accept solution" if it allows 2 then will make @harshnathani as well.
Before closing this, how to get master in DAX 😞 , any helpful links will be highly appreciated.
Actually i'm sturggle lot to write a DAX 😞 😞 😞
Thanks alot.
Hi @amitchandak
Thanks for sharing more information if it was in differnt tables.
Thanks,
Rams
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 🙂
Hi @nandukrishnavs,,@amitchandak ,@harshnathani
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
@ramshoney1225 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 🙂
Ya, I created calculated columns :|.
My assumption is wrong , i though that it will do row by row SUMX used and i created a calculated column.
Ok will create that in measure, let you know 🙂
User | Count |
---|---|
96 | |
87 | |
78 | |
73 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |