I normally use calculated fields in Pivot tables whenever possible. DAX is my next challenge into which I will jump right away with your videos. Many thanks for sharing.
So far I have been doing way too much analysis in Power Query. I am starting to build up more and more analyses with Dax. Your videos are the best training for this. Thank you.
Nope. Just been giving away the free education, all 3,500 videos for the past 15 years here at UA-cam. All I charge is a Thumbs Up ; ) Keep watching and learning.
Thanks Mike...three great ways. On smaller data sets I use the helper column method but usually I will is PQ and PP. I find it easier to keep my raw dataset in tact then remove columns in PQ and add measures in PP. Thanks for the video....
Excellent Mike! Great to know all three methods. Love the Power Pivot / DAX solution, but there are arguments for each, depending on the user and the problem. I'm rerunning your MSPTDA series now, so can't get enough of all the possible ways to solve. Thanks always for your top notch, interesting, entertaining and informative videos and playlists :)) Thumbs up!!
Would this be very different if the revenue and COGS appear in the rows (like a list of transactions) instead of in the example in separate columns? Can you still easily create calculated fields?
exactly, i used to use SQL to query large database to analyze but PBi with DAX is lot easier for data analysis purpose. after DirectQuery became available, life is good.
@@jameszhou162 How do you get a long with Direct Query? Because I thought in Power BI that when you use Direct Query the time intelligence functions did not work because the direct query could not translate and send back to SQL database. Maybe this has changed?
Thanks Mike, good as usual with you! maybe for next video's explain with an example the differences between profit % differences, per line, product or productgroup. If you use "average" you have to be very carefull (as you said in the video). This is actually a math/statistics issue, not an Excel issue....
I always use calculated ranges and calculated items. To me, it’s like using the Power Query user interface rather than writing M Code. I have never used DAX code before but now I am interested in trying it out!
Here are a few good time videos that I have made: Basic Excel Business Analytics #16: Count Transactions by Hour Report & Chart ua-cam.com/video/3YeoX1Cl7Og/v-deo.html MSPTDA 22: DAX Data Modeling to Create Date & Time Dimension Tables, Server Downtime Visualizations ua-cam.com/video/RhPzRk-wEYw/v-deo.html
Hi, do you have any videos that gives tutorial to calculate items in a column? f.e. the Revenue and COGS are both in 1 column but i want to generate Gross profit margin from these items
Can we use dax formula not using calculated field to calculate sum of two column in multiple level column field.. For example : column name A contain sub column B, C, & D. And also there is column E that in the same level like column A.. And I want to add column F that the position is the same level like A & E which column F is the sum of only column B & C?
@@excelisfun @ExcelIsFun in pivot table column, there are categoryi 1 and 2, category 1 contain 3 column product A, B, C.. Category 2 contain 2 column product D & E.. Can we use dax formula to create sub category 3 column that is the sum of product A & C?
@@GeertDelmulle @Geert Delmulle column name Asia Continent contain 3 sub-columns countries Japan, China, India. Column America Continent contain 2 sub-columns countries USA, Canada.. Can we use dax formula to make another continent column names Asia-2 that is the sum of country China and Japan?
@@evilangel4136 Let me rephrase that: • you have a “continent” column, values at hand: Asia, America • you have a “country” column, values at hand: Japan, China, USA, Canada (depending on the value in the first column) Your question: can you create a calculated column that is conditional depending on the values in the “country” column (or any other column for that matter)? My Answer: yes. DAX is very powerful. Perhaps you want to conditionally sum some numerical valued column. In DAX you can do this 2 ways: • first create a calculated column inside your table and then create a simple SUM-measure on the latter column. • second: do it all in a measure.
Use the "fRevenue2" table from EMT1729 and add a COGS column using the formula =ROUND([@Revenue]*(RANDBETWEEN(45,51)/100),2). Copy and Paste Values of the entire column to prevent it from recalculating. It's not exactly what's shown, but close enough. If you're following along, check the Revenue fields, obviously the COGS fields and any other fields using that value will be different in the video. I love the Data Model!
I’ll use the DAX way, somehow it appears simpler. How far we’ve come (we’ve done much more intricate measures by now - want a measure that calculates an entire histogram?... No problem :-).
Undoubtedly, DAX is the best method. But learning and mastering DAX has a steep learning curve. Could you please cover various DAX formulas in detail in your future videos?
Is that your preferred method? As time moves on, I certainly prefer the DAX method too. It is just so each because the formula just always have correct name and formatting : )
your videos always resolve my problem in time. Great thanks. Mil thumbs up for Mike
You are welcome!!!
You are a life saver 🙏🏻🙏🏻 thank you
You are welcome!
Amazing tutorial. I wish more instructors would explain things the way you do by comparing multiple methods of achieving the same result.
I normally use calculated fields in Pivot tables whenever possible. DAX is my next challenge into which I will jump right away with your videos. Many thanks for sharing.
So you use Calculated Fields often? Cool : )
super 😀, thank you so much for you content. I liked the DAX measures.
Superb mr mike 💯💯💯💯👍🏻👍🏻👍🏻👍🏻👍🏻
: ) : ) : ) : )
So far I have been doing way too much analysis in Power Query. I am starting to build up more and more analyses with Dax. Your videos are the best training for this. Thank you.
Glad I can help, Frank!!!!
All the techniques are beautiful and are organized in ascending order or beauty and awesomeness. Thank you, Mike!
You are welcome for the ascending beauty of Excel fun : ) : )
Thank you for always including different solutions for different skill levels and excel versions.
You are welcome : )
I hope you're making lots of money from all these free tutorials. I would not mind paying you to teach me.
Nope. Just been giving away the free education, all 3,500 videos for the past 15 years here at UA-cam. All I charge is a Thumbs Up ; ) Keep watching and learning.
Thanks Mike...three great ways. On smaller data sets I use the helper column method but usually I will is PQ and PP. I find it easier to keep my raw dataset in tact then remove columns in PQ and add measures in PP. Thanks for the video....
You are welcome, Matt!!!! : ) : )
Excellent Mike! Great to know all three methods. Love the Power Pivot / DAX solution, but there are arguments for each, depending on the user and the problem. I'm rerunning your MSPTDA series now, so can't get enough of all the possible ways to solve. Thanks always for your top notch, interesting, entertaining and informative videos and playlists :)) Thumbs up!!
You are welcome, Wayne!! Cool to hear that your have boomeranged back to MSPTDA : )
I used to use the helper column, but now, that is history :)
Cool! Calculated Field, then? or DAX?
@@excelisfun both look awesom-azing !! :) but I have to test them in order to decide :) do you prefer one over another?
@@yosra782 I prefer DAX because I can add name, format and formula all in one place : )
@@excelisfun isn't that EXCELlent? :) Can't wait to give it a shot
@@yosra782 Yes ; ) EXCELlnet!!!!
Thank you for the video Mike. I enjoyed all 3 methods. I would probably use the helper column method because I feel the calculations are more visible.
Good point about the visual row by row amounts. More transparent : )
Thank you Mike..Always great video! User friendly explanation and a lot of stuff fun to try and extremely useful... Nice job from Italy!!
You are welcome, Giulio : )
Thanks Mike for this amazingly EXCELlent video.
You are welcome, Fellow Teacher : ) : ) : )
Muito obrigado!
Tks!
: ) : )
Very informative
DAX is cool because it's more efficient and effective
You are the best of the best. Aways Thank You!!!!!
Very well Mike, if I can, I would give you one million likes.
Thank you for the one million likes : )
Would this be very different if the revenue and COGS appear in the rows (like a list of transactions) instead of in the example in separate columns? Can you still easily create calculated fields?
Boom!Super Fun With PivotTables...Thank You Mike :) P.S Dax Formula Version :):)
: ) : ) Do you use DAX formulas often, darryl?
Thumbs up! My fav is DAX 🙂
Excellent Video Mike. That was fun watching!! : ) : )
Glad you had fun, Formula Guy, John Borg : ) : ) : )
I’d use DAX every time now. Especially on very large datasets
exactly, i used to use SQL to query large database to analyze but PBi with DAX is lot easier for data analysis purpose. after DirectQuery became available, life is good.
RJ Bush,
How about on small data sets? If I have a bunch of reporst, I am tending to use the Data Model and DAX even on these small data sets.
@@jameszhou162 How do you get a long with Direct Query? Because I thought in Power BI that when you use Direct Query the time intelligence functions did not work because the direct query could not translate and send back to SQL database. Maybe this has changed?
@@jameszhou162 hi could you suggest me s site for DAX as a beginner?
Thank you for the explanation , I need to work on 2023 vs 2024 data in power pivot (cant use calculated fields) ,pls guide me what do we do here
Great video as always!
Man, I really need to keep this info in my head, always seems to leak out!
You are so correct about info always leaking out of our brains... You can always boomerang back and re-watch : )
Great, Thank you Mike!👍
You are welcome, Luciano : ) : )
Amazing as always. Thanks.
You are welcome, Teammate!
Pivot Table / Power Pivot (DAX)...can't decide 😁 I like both!
50/50 is good. More fun that way!!!
Excellent Mike!
Thanks, Chris : ) : ) : )
Very good and informative
Thank you!
Glad you like it, Ivan : ) : )
Thanks Mike, good as usual with you! maybe for next video's explain with an example the differences between profit % differences, per line, product or productgroup. If you use "average" you have to be very carefull (as you said in the video). This is actually a math/statistics issue, not an Excel issue....
Yes, you are right, it is a math stats issue. An example of a column with the error would have helped illustrate : )
Thanks Mike, I would definitely run it with DAX ;)
DAX is so convenient: name, format and formula all in one place : )
I always use calculated ranges and calculated items. To me, it’s like using the Power Query user interface rather than writing M Code. I have never used DAX code before but now I am interested in trying it out!
Great! If you use the same calculation in multiple reports, DAX is really efficient : )
Hi Mike ,I have a data set where revenue , cos are shown in row and iterations please advice how to calculate gross margin in power pivot
Couldn’t you for method 1 - just right click and have the pivot return % of revenue to get the gprofit%?
Sir, can we multiply 2 fields (Revenue * COGS) by using the pivot table calculated field??
I don't often use Calculated Fields any longer. I'm most likely to send the data through Power Query into Power Pivot like your instruction provided.
I suppose you could have completed your calculations in Power Query then Loaded directly to a Pivot Table.
Thanks Mike! will work with DAX and learn it. Does this DAX reduces the file size & efficiency compare to Calculated field & Pivot table columns.
Great video Mike. Can you do one with Time Calculations please?
Here are a few good time videos that I have made:
Basic Excel Business Analytics #16: Count Transactions by Hour Report & Chart ua-cam.com/video/3YeoX1Cl7Og/v-deo.html
MSPTDA 22: DAX Data Modeling to Create Date & Time Dimension Tables, Server Downtime Visualizations ua-cam.com/video/RhPzRk-wEYw/v-deo.html
Hi, do you have any videos that gives tutorial to calculate items in a column? f.e. the Revenue and COGS are both in 1 column but i want to generate Gross profit margin from these items
I thought i did at 4:29 minute mark in this video.
@@excelisfun that is calculating fields (column) and not items in a column (filtered rows in 1 col)
Dax the way to make an Excel video!
I can hear that toon, un huh, un huh, I like it!!!!
@@excelisfun Lol
Thank you !!!
I usually use pivot table & calc field, as my data is usually small
Can we use dax formula not using calculated field to calculate sum of two column in multiple level column field.. For example : column name A contain sub column B, C, & D. And also there is column E that in the same level like column A.. And I want to add column F that the position is the same level like A & E which column F is the sum of only column B & C?
I am sorry, but I do not understand what you are asking.
@@excelisfun @ExcelIsFun in pivot table column, there are categoryi 1 and 2, category 1 contain 3 column product A, B, C.. Category 2 contain 2 column product D & E.. Can we use dax formula to create sub category 3 column that is the sum of product A & C?
Too many letters of the alphabet in this question. Can you reduce it to its simplest form?
@@GeertDelmulle @Geert Delmulle column name Asia Continent contain 3 sub-columns countries Japan, China, India.
Column America Continent contain 2 sub-columns countries USA, Canada..
Can we use dax formula to make another continent column names Asia-2 that is the sum of country China and Japan?
@@evilangel4136 Let me rephrase that:
• you have a “continent” column, values at hand: Asia, America
• you have a “country” column, values at hand: Japan, China, USA, Canada (depending on the value in the first column)
Your question: can you create a calculated column that is conditional depending on the values in the “country” column (or any other column for that matter)?
My Answer: yes. DAX is very powerful. Perhaps you want to conditionally sum some numerical valued column.
In DAX you can do this 2 ways:
• first create a calculated column inside your table and then create a simple SUM-measure on the latter column.
• second: do it all in a measure.
I will definitely use DAX
DAC is cool : )
Amazing ❤️
Glad it is amazing, usman : )
Hi, I can't get to that file to download... has it been deleted or moved?
Use the "fRevenue2" table from EMT1729 and add a COGS column using the formula =ROUND([@Revenue]*(RANDBETWEEN(45,51)/100),2). Copy and Paste Values of the entire column to prevent it from recalculating. It's not exactly what's shown, but close enough. If you're following along, check the Revenue fields, obviously the COGS fields and any other fields using that value will be different in the video.
I love the Data Model!
Thanks for letting me know, Maureen : ) I just fixed it and the files are available for download and gross profit Excel fun : )
I agree with you sir excel is fun
Yes!!! I LOVE to hear that : )
I’ll use the DAX way, somehow it appears simpler.
How far we’ve come (we’ve done much more intricate measures by now - want a measure that calculates an entire histogram?... No problem :-).
No problem becasue it is fun : )
You are right, it has been a long time that we have been able to make Data Model PivotTables and DAX formulas.
Undoubtedly, DAX is the best method. But learning and mastering DAX has a steep learning curve. Could you please cover various DAX formulas in detail in your future videos?
: ) I have a LOT of DAX videos already. Here is a playlist: ua-cam.com/play/PLrRPvpgDmw0nglJ9yX2XT5-K1A_AKHpvW.html
@@excelisfun Thank you very much. This is a gold mine of information :)
@@ramkrishna133 You are welcome : )
Once you DAX, you'll never go back! 😉
So TRUE. Even on small data sets when I have a bunch of reports, I tend to use DAX now : )
DAX definitively !
Is that your preferred method? As time moves on, I certainly prefer the DAX method too. It is just so each because the formula just always have correct name and formatting : )
@@excelisfun : Yes and you can use several times the same mesure without selecting the format
DAX
: )
Pivot table.
: ) : ) : )