Excel 2013 PowerPivot Basics #09: Measures (Calculated Fields) & DAX Functions 9 Examples

[@hahahacho9474]

Sir, you're doing more good to the world than you realize. Thank you.

[@excelisfun]

+hahahacho You are welcome!

[@excelisfun]

Excel 2013 PowerPivot Basics #09: Calculated Fields using DAX Functions 9 Examples Download file: people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. See how to create 9 different DAX functions or Calculated Fields 1. (00:08 min mark) Overview of Reports we need to create 2. (01:05 min mark) Calculated Fields Overview 3. (01:58 min mark) Calculated Column For Net Revenue using ROUND and RELATED function and table names and columns names with multiplication operators. Net Revenue =ROUND(RELATED(dProducts[RetailPrice])*(1-fSales[RevenueDiscount])*fSales[Units],2) 4. (03:22 min mark) Reminder of “Row Context” 5. (04:47 min mark) DAX function = Data Analysis EXpressions 6. (05:05 min mark) Calculated Field 1: Total Net Revenue:=SUM(fSales[Net Revenue]). 7. (06:59 min mark) Calculated Field 2: Total COGS:=SUMX(fSales,ROUND(RELATED(dProducts[StandardCost])*fSales[PercentOfStandardCost]*fSales[Units],2)) 8. (07:24 min mark) Calculated Field or Calculated Column? Which one to use? 9. (09:05 min mark) “Row Context” in Calculated Field, use X functions like SUMX and COUNTX. 10. (10:55 min mark) Calculated Field 3: GrossProfit:=[TotalNetRevenue]-[TotalCOGS] 11. (10:55 min mark) First use of Calculated Field in another calculated field: we are sure to use our convention of Square Brackets means that it is a Calculated Field!!!! And not a column name. 12. (11:45 min mark) Edit Calculated Fields in Measure Grid. 13. (12:25 min mark) Calculated Field 4: GrossProfitPercentage:=DIVIDE([GrossProfit],[TotalNetRevenue]) 14. (13:42 min mark) First Report: Net revenue, COGS, Gross Profit, GP % formulas with Four Criteria: Yea, Month, Product and Color Criteria (Product and Color on Slicer, Year and Month in PivotTable Row and Column Headers). 15. (16:30 min mark) Examine what “Filter Context” means from a cell in PivotTable with Four criteria. Look at Formulas and Design View to see relationships between tables to try and understand of the filtering of the Columnar database works based on the Criteria that is dropped into the PivotTable. 16. (17:35 min mark) Calculated Field 5: DISTINCTCOUNT function to count actual days where sales were made. Number Of Sales Days:=DISTINCTCOUNT(fSales[Date]) 17. (18:45 min mark) Calculated Field 6: Total Number of Days Over 4 Year Period:=COUNTROWS(dCalendar) 18. (19:40 min mark) Calculated Field 7: Average Sales per Sales Day:=ROUND(DIVIDE([Total Net Revenue],[Number of Sales days]),2) 19. (20:34 min mark) Start Report 2: Distinct Count and Ave. Sales per day by YEAR. 20. (21:47 min mark) Compare Excel Formula and DAX formula (Calculated Field) for Percentage Change. See IFERROR in Excel and how it can handle two data types. 21. (24:00 min mark) Calculated Field 8: Total Net Revenue From Last Year:=CALCULATE([Total Net Revenue],SAMEPERIODLASTYEAR(dCalendar[Dates])). 22. (24:00 min mark) CALCULATE will allows us to change the Filter Context. 23. (26:08 min mark) % Change for Net Revenue:=IF([Total Net Revenue From Last Year]=0,BLANK(),[Total Net Revenue]/[Total Net Revenue From Last Year]-1) 24. (27:50 min mark) Look at IFERROR 25. (28:40 min mark) IF rather than IFERROR 26. (28:05 min mark) BLANK function 27. (28:59 min mark) Conclusion

[@excelisfun]

I forgot to mention: In PowerPivot: BLANK DAX Function represents Empty Cell, Missing Value, Null (Database "unknown value") BLANK = Empty Cell, Missing Value BLANK is not a Zero Length Text String BLANK is not an Error

[@islam48ify]

I THANK GOD BECAUSE I KNOW ENGLISH TO HEAR AND LEARN FROM U SIR

[@jakael02]

This dude is an Excel living legend.

[@excelisfun]

Just a guy having fun with Excel ;) Glad you like the videos!

[@joannpaules3642]

It's been a while since you posted these videos but they are still extremely helpful. I'm using Excel 2016 and have no trouble following the video. And by the way, THANK YOU for including a Start and Finish version of the file! It's nice to be able to check my work.

[@MrPrakashsomani]

It’s really amazing to see your skills for making comeplex problem so simple... can you help me know where can I get your file for practice? Many thanks.

[@gorang_vashist]

i am still not understand why to use measures? these calculayions can also be done in normal privot table by adding another colum of calculated fields. can you please explain the diferences

[@sarienvanschalkwyk6318]

Thank you! Thank you! I’m new to Power Pivot and have watched a couple of videos before this one. I just didn’t get the difference between SUM and SUMX. But with this video...the lights finally went on. Great stuff!

[@excelisfun]

Glad to help!!!!

[@simonchouinard6803]

Hey for the % variance I normally use the calculated field option under the Analyze tab of the pivot table in excel but when I use your formula in power query it add up everything in my pivot table so I get like -53254% instead of -1.2%.. Any way to solve that?

[@ryanpark4000]

What about same period last week? There's no convenient formula for that so how is it done?

[@cruzmdelay]

Hello Mike, can I get your help figuring out why the Calculated Field is grayed out? I cant select it.

[@LxcsapCuellar]

You are the best... I have been looking at your videos and they opened my eyes on how powerful it is... one question my excel ( power pivot ) is not so quickly as yours ... any recommendation ...?

[@lisat7850]

Hi, i have excel 2010 and an error appeared saying that i am unable to load/ work on the data in the data model because the it was done on a newer version. do you know a workaround this other than upgrading to a newer version ? thanks very much.

[@kisimlama1095]

Mr ExcelFun, just to double confirm how does work sameperiodlastyear function? If I have sales in 2015 only for 6 month but in 2016 I have through out the year. So in this case does sameperiodlast year will calculate sales of 2016 those match with 2015 month? If does not work such which function use to solve this requirement.

[@MrPrakashsomani]

kisim lama as we can make out from the video there are different number of days each year and this formula gave exact same number as last year so I believe irrespective of no. of days , this formula gives sales for entire year

[@annagala194]

Thank you so much for putting these videos together! I was trying to get through the entrance barrier of "Power BI" products family several times, but was not to - there was always something missing and it was always very confusing. I think I was missing understanding of PowerPivot :) - I think I finally made it, after spending a little bit more than two hours watching these videos.

[@excelisfun]

Glad the videos is useful for you!

[@vuaus]

Hi Mr. Excelisfun. Do you have any videos about statistics? I think Excel is absolutely able to do what SPSS can. Thank you!

[@excelisfun]

Yes, I have 150 videos about basic stats in this playlist: kzbin.info/aero/PLFAD0C5745D4A1F60

[@vuaus]

ExcelIsFun Thanks a lot!!!

[@excelisfun]

Quoc Trung Bui Le You are welcome!

[@canefan17]

What is the PercentOfStandardCost? What's the calculation for that and what is it?

[@asthatripathi9211]

Hi Mike, just one question.. How do I add two different measures from two different table. For example Table a has sales1 data and table 2 has sales data, but they are from different server..i want to show the combine sales for two measures that I created in that table for different products, how do I add them?? simple sum(measure1) +sum( measure2) is not working..

[@kisimlama1095]

Mr excelfun, I have big data in ftable and I have income rate dimension table with condition rate in the condition column I have % n dollar sign and other column I have % value and absulate value.Now I want calculate if my condition rate is equal % then calculate income and give me the result otherwise show me the absulate value from dimension table. When I use if function it is throwing error. Need your help please.

[@JulsOsorio]

How can I to do a sum with a calculated field?. But only must sum unique values.

[@dasrotrad]

I am having trouble opening PowerPivot09-CaculatedFieldsStart.xlsx The application freezes. If I try and open PowerPivot09-CaculatedFieldsStart.xlsx, I have the message "Security Warning. External Data Connectons have been disabled.Can you please advise how to locate the source data for this file? It must be in an earlier lesson. Thank you.Robert

[@aantanuwijaya517]

Great tutorial. Wanna ask. Is there any way to update mysql database from excel? Now I only know connect from mysql to excel.

[@HumbaHarry1]

If I'd like to calculate the Total Net Revenue without the helper column [Net Revenue] (Video @6:00min), one could use =SUMX(fSales;RELATED(dProducts[RetailPrice])*(1-fSales[RevenueDiscount])*fSales[Units]) So my question is: Why does it not work if I'd write: =SUMX(RELATEDTABLE(dProducts);dProducts[RetailPrice]*(1-fSales[RevenueDiscount])*....? Why do I have to use fSales as the chosen table and not dProducts? I guess it's syntax, but I really want to develope a full undertanding for PowerP. and how the functions work. THX

[@JulsOsorio]

Hi Michael, my problem is I have my own transactions table, and this table has a 117.000 rows and 10 columns. One of this columns, have information about number of homes per city, but this number of homes can repeat many times inside the 117.000 rows. So I need sum only the unique values for this column (number of homes per city). You can give me an idea, how can I make a sum for homes per city column with unique values? Thank you for your help. Happy week.

[@HumbaHarry1]

I'm pretty new to PowePivot, but I guess you could use the distinct function, or u need to find a criteria which makes your selection for each city just once Someone has a answer to my original question above?

[@JulsOsorio]

Thanks !!! Michael !!! Best regards my friend

[@suprajakumari700]

Can we pass dynamically value in the table expression of sumx formula.

[@excelisfun]

***** , you are welcome!

[@lhofweber]

Mike, why did you use Round Function when you could have just gone to the formatting? I thought that the Power Query always remembered your preferences that you chose.

[@brianxyz]

The formatting options just change what's displayed not the underlying number like Round does. There's a big difference. If you search the channel there's videos giving detailed examples of how you can get the wrong answer if you're working to say just two decimal places.

[@lynnknight9379]

That's awesome. How do I perform simple calculation for each column such as: sum for Collections for the month, charges, ect. ? In regular pivot table we just drag and drop to get the sum amount. Please advise.

[@MrPrakashsomani]

Lynn Knight insert a calculated field like he did for total revenue

[@adamowiczjacek6543]

Great tutorial!!! I've a question. Could You explain the diiferences between. TotalCOGS2:=sumx(fSales;ROUND(fSales[Units]*fSales[PercentOfStandardCost]*RELATED(dProducts[StandardCost]);2)) =>TotalCOGS2: $37 993 723,82 and TotalCOGS:=sumx(fSales;ROUND(RELATED(dProducts[StandardCost])*fSales[PercentOfStandardCost]*fSales[Units];2))=> TotalCOGS: $37 993 815,19 In my opinion these measures shoud be the same. Why are these measure diffrent? Did i mistake or something is in Power Pivot?? Thanks for explaning

[@excelisfun]

As I look at your two formulas typed in the above comment, I can soo NO reason that they are different... 2*3*8 = 8*2*3... But maybe I am missing a parenthesis or something. Can you e-mail me your file to excelisfun at gmail?

[@excelisfun]

Wow!!! It turns out that in Data Modeling you have to use the same Data Type on all numbers, otherwise the thousand of years rule of Communicative Property Of Multiplication does not hold!?!?! I post a comment at mrexcel.com/forum and Own helped me. Here is the full dialog: Dear Team, I have a table in the Power Pivot Data Model. I have created two Calculated Columns that are supposed to calculate the Line Item COGS for each sale. The two Formulas are identical, except for the order in which multiplication is done. For some rows in the table, the two formulas yield different answers. If I try the same two formulas in an Excel Sheet with Excel Spreadsheet Formulas every line yields the same answer. Why is it that in a DAX Calculated Column a formula would yield a different number simply by changing the order of how the numbers are multiplied? Here is Calculated Column Number 1: LineCOGS01 = ROUND(RELATED(dProducts[StandardCost])*fSales[PercentOfStandardCost]*fSales[Units],2) Here is Calculated Column Number 2: LineCOGS02 = ROUND(fSales[Units]*fSales[PercentOfStandardCost]*RELATED(dProducts[StandardCost]),2) The only difference between the two is for #1 we mutliply Cost*Percent*Units, and for #2 we multiply Units*Percent*Cost The actual numbers used in the calcualtion are: LineCOGS01 = 8.25 * 0.953 * 131, rounded = 1029.96 LineCOGS02 = 131 * 0.953 * 8.25, rounded = 1029.95 If I export the table to an Excel Sheet and use Excel Spreadhseet Formulas I do not get this error. Here are the formulas I used: ExcelLineCOGS03 = =ROUND([@StandardCost]*[@PercentOfStandardCost]*[@Units],2) = 1029.95 ExcelLineCOGS04 = =ROUND([@Units]*[@PercentOfStandardCost]*[@StandardCost],2) = 1029.95 Here is a link to the file that contains the two Calcualted Columns with this "Order of Multiplying Difference Error": people.highline.edu/mgirvin/...ifferenceError... In the Data Model, the table named fSales contains the two Calculated Columns named LineCOGS01 and LineCOGS02. Any ideas? Sincerely, Mike Girvin Hi Mike, The underlying reason for the difference is that the column dProducts[StandardCost] is of type Currency (aka Fixed Decimal Number), while the other columns are of type Decimal. In particular: 1.The Currency type stores decimal values with up to four decimal places (actually stored as integers divided by 10,000). 2.When a Currency value is multiplied by a Decimal value in DAX, the result is a Currency value, resulting in potential loss of precision. 3.The result is that the order of multiplication of 3 or more numbers matters when Currency and other types are mixed. I am assuming that DAX multiplication is performed from left to right, so that in your case, the two calculations are as follows ( [C] indicating Currency and [D] indicating Decimal): •LineCOGS01 = ROUND ( 8.25[C] * 0.953[D] * 131[D], 2) = ROUND ( 7.8623[C] * 131[D], 2 ) = ROUND ( 1,029.9613[C], 2 ) = 1,029.96 •LineCOGS02 = ROUND ( 131[D] * 0.953[D] * 8.25[C], 2) = ROUND ( 124.843[D] * 8.25[C], 2) = ROUND ( 1,029.9548[C], 2) = 1,029.95 Since LineCOGS02 multiplies by the Currency value last, the rounding to 4 decimal places (as a result of conversion to Currency) happens later. To get the same result from these two formulas (and match Excel), you could change dProducts[StandardCost] to Decimal type. By the way, I had to look up the detail on behaviour of these different types in this SQLBI article www.sqlbi.com/articles/under...rsions-in-dax/ Regards, Owen Dear Owen, Brilliant, Owen : ) Thank you very much for unearthing the issue here. However, the implication seems disturbing. It seems to me that in order to not disrupt all of mathematical history (commutative property of multiplication), that when designing Data Models we will have to assign a consistent Data Type for all numbers that might be used for multiplying and dividing. Is this how you approach designing your Data Models? Again, thanks a million! Sincerely, Mike Girvin

[@adamowiczjacek6543]

@@excelisfun WOW WOW!!! GOOD JOB FOR YOU AND OWEN THANKS FOR EXPLANATION!! REGARDS JACEK

[@rgparisoto]

Hi ExcellFun! Excellent videos!!! Please, help me with something, don't we have a function like SAMEPERIODLASTYEAR, to use use MONTHS, like, SAMEPERIODLASTMONTH? Thank you and regards from Brazil.

[@jaclynpugh4209]

I have followed your exact steps for SAMEASLASTYEAR and I cannot get the dax formula to work at all...I have recreated my datefield to see if it was the problem and the formula is just not working...do you have any other recommendations? thank you

[@MrPrakashsomani]

jaclyn pugh just ignore iferror function from this formula and try ... I believe integrating lots of formula in one go making newer formula more complex...

[@manujsaxena2208]

Hi Mike, in Sameperiodlastyear formula, why can't we use fSales[Dates] instead of dCalendar[Dates]?

[@excelisfun]

The Time Intelligence Functions, like SAMEPERIODLASTYEAR require a Date table with a single unique list of dates for each full year. In internal code for the Data Model to work efficiently on Big Data Requires it.

[@HarveyFrench]

Such a useful video. I just read a microsoft book on DAX which was hard going. Now I know more, I wish the book had explained it better and differently. This video gives great examples and the path being followed make a lot of sense to me. Thanks.

[@excelisfun]

You are welcome!

[@ashishtiwari1912]

nyc video.. Is there any difference between standard cost and normal cost?

[@phareesh84]

I guess with Related function we can look up only numeric values or can we lookup string values also, if not can you please let me know with which function we can lookup string values.

[@excelisfun]

RELATED can look up and return strings. What is the trouble you are having?

[@davidhansen527]

Why does I first typed in "Total Revenue from Last year:=CALCULATE(fSales[Total Net Revenue],SAMEPERIODLASTYEAR(fSales[Date]))", pulling the date from the sales table, instead of "Total Revenue from Last year:=CALCULATE(fSales[Total Net Revenue],SAMEPERIODLASTYEAR(dCalendar[Dates]))". Total Revenue from Last year:=CALCULATE(fSales[Total Net Revenue],SAMEPERIODLASTYEAR(fSales[Date])) when placed in a PivotTable will only total in the grand total Row. Anyone know why that is?

[@nishantkumar9570]

Awesome! How to establish relationships between two tables if we have duplicates of related entity in both the table.

[@MrPrakashsomani]

Nis 007 you should have unique header

[@shorn77777]

I Have been searching the whole internet for using calculated field in datamodel. coz it was greyed out. but now i got it. thnks. i wanted to use distinct count in other calculated fields. today it happened. how about the grouping and non grouping of dates. you can break its code too?

[@milosdejanovic2196]

Hi, When i use =if(something=0, blank (),...) i get in pivot massage instead of empty cell. Is it something that I should modify in settings to get empty cell?

[@MrPrakashsomani]

Miloš Dejanović I think he has created a field ... though I suggest to put ‘’’’ instead

[@ahmedal-dossary4386]

Awesome as always Mike..!! I need your thought on below scenario: Is there a faster way to copy and paste a formula across in a calculated column? It’s a lengthy process in data model unlike formula bar in excel, especially when you have +30 columns and perform a measure for each to take advantage of number formatting and get rid of ‘sum of’ in pivottable.

[@MrPrakashsomani]

Ahmed Al-Dossary you should use tables which automatically replicates the formula in entire column

[@ahmedal-dossary4386]

Dear Prakso Prakso, what about in data model/powerpivot?

[@roykeidarj]

Hi Mike, I'm trying to enable PowerPivot in excel 2016 (365 ProPlus) and under the Add the COM-Add-ins I don't have the option of ''MIcrosof power pivot for excel''. Do you think it's related to the language I'm using (which is Hebrew). And if so - is there a way to change my excel language to english? Thanks in advance

[@excelisfun]

You will have to contact Microsoft Help about that. I am not sure about the Hebrew version. If you contact them, sometimes the sales reps have NO idea what they are talking about, so if that is the case, make sure you ask for there manager or someone else who can actually answer the question.

[@foxybark]

I wonder if this will work for me. I'm attempting to annualize a dollar amount using dax. I want amounts for multiple years and the count of months vary in each year. my theory is this: (sum (amount) / distinct count of months in each year) * 12. I hope your method works. I'm at my wits end.

[@BillSzysz1]

I had a little break... but I'm back :-))) Many many thanks for all your videos about PP. Very impressive and interesting subject !!!

[@excelisfun]

Lots of "You are welcome"s to you Bill!! :)

[@anvarudeenjamaludeen993]

Thanks Mike, waiting for 10, 11, 12 & 13

[@excelisfun]

You are welcome!

[@rorysketch3056]

Seriously good material Mike - thanks. I've managed to persuade my boss we need it. Just the small matter of delivering now...!

[@davidjuliovergaramarin3996]

Muchas gracias por sus videos son espectaculares y ayudan mucho

[@excelisfun]

You are welcome, David!

[@nadermounir8228]

Great video 📹 thank you Mike

[@santiagobardelli5675]

Outstanding explanation, BOOOMMM

[@jasminezhang8093]

Thank you very much for all your videos, they are really useful! I have learnt a lot.

[@DougHExcel]

Great examples, thanks Mike!

[@excelisfun]

Glad you like it! I made this video a long time ago...

[@natraj6716]

Thanks a lot sir for sharing your valuable knowledge to us.

[@jongcheulkim7284]

This is amazing!! Thank you so much.

[@excelisfun]

You're so welcome!

[@xinlu102]

u sir u are a hero, u save mine and my buddy's life , xoxoxo love you

[@AIshaAtolba]

man !! Many thanks for the effort you're making :)

[@excelisfun]

You are welcome for the effort, Alsha!!!!

[@uma.naturelover]

Thank you and good work!

[@konstantinospileidis636]

Amazing "pppprrrrrpppp" and "pouppppp" sounds !!!!

[@excelisfun]

+ΚΩΝΣΤΑΝΤΙΝΟΣ ΠΗΛΕΙΔΗΣ Makes it more fun!!

[@konstantinospileidis636]

The best teacher all the time!!!

[@excelisfun]

Thanks for the kind words!!!

[@shorn77777]

are you from MS

[@thientran-rd3qk]

@@excelisfun ur the best Excel teacher :)

[@lynnknight9379]

Forget to add sum for each account.

[@wildecorrea6477]

Great!!!

[@willystorato535]

Many Thanks Mike!

[@excelisfun]

+willy Storato You are welcome!

[@LuisFurtadoOficial]

Thanks for this fantastic video!

[@excelisfun]

You are welcome!

[@jasminezhang8093]

The way you calculate % change is not quite right. For instance, Year 2014 is 1000, Year 2015 is 2000, if you would like to calculate the % change from Yr2014 to Yr2015, it should be [(2000-1000)/1000]% = (2000/1000 - 1)%, instead of 1000/2000 - 1.

[@excelisfun]

+Jasmine Zhang You are incorrect in saying that End/Beg-1 will not yield the correct answer. This is a math truth: (End-Beg)/Beg = End/Beg - Beg/Beg = End/Beg - 1. Not only that, but the formula in the video is correct: [Total Net Revenue]/[Total Net Revenue From Last Year]-1

[@jasminezhang8093]

+ExcelIsFun Sorry, I mis-remembered the formula in the video as Beg/End - 1, but you are right, End-Beg/Beg = End/Beg - 1 is correct! Sorry

[@patricknyamu6110]

Great work

[@excelisfun]

Glad you like it!

[@krn14242]

Thanks Mike

[@excelisfun]

You are welcome World Record Holder!!

[@Fitness_Cars]

My lord how can reach you with a complex situation....comment box wont help to explain the problem. I need send you the excel sheet.

[@55DONDONDON]

BOOM