Iterating Functions Deep Dive - SUMX, AVERAGEX, MINX, MAXX
Vložit
- čas přidán 2. 07. 2019
- In Power BI you must understand the difference between aggregating functions and iterating functions very well.
In this tutorial we'll be diving into iterating functions including SUMX, AVERAGEX, MINX & MAXX. Well be seeing the various ways they can be used and also going into detail how results are actually calculated when using these formulas.
These are some crucial concepts in Power BI when using DAX, so certainly dive into this content in detail and start practicing when you can using these techniques.
**** Video Details ****
1:32 - iterating using SUMX
3:08 - using an alternative approach to SUMX
7:00 - iterating through tables
8:58 - iterating using MINX
9:46 - iterating using MAXX
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ulti...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ulti...
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-...
FREE - Power BI Resources - enterprisedna.co/power-bi-res...
Enterprise DNA Membership - enterprisedna.co/membership
Enterprise DNA Online - portal.enterprisedna.co/
Enterprise DNA Events - enterprisedna.co/enterprise-d...
**** Related Links ****
Using Complex Logic Inside Iterating Functions - Advanced DAX - • Using Complex Logic In...
How To Use Iterating Functions To Solve Total Issues In Power BI - DAX Concepts - • How To Use Iterating F...
Advanced Logic Within DAX Iterating Functions - Detailed Example - blog.enterprisedna.co/2017/12...
**** Related Course Modules ****
Mastering DAX Calculations - portal.enterprisedna.co/p/mast...
Solving Analytical Scenarios w/Power BI & DAX - portal.enterprisedna.co/p/solv...
Unique Analytical Scenarios - portal.enterprisedna.co/p/uniq...
**** Related Support Forum Posts ****
Combining Aggregating & Iterating Functions To Calculate Median - forum.enterprisedna.co/t/comb...
Creating Plan / Actual For Year & Months - forum.enterprisedna.co/t/crea...
Calculating Average And Using DateMonth - forum.enterprisedna.co/t/calc...
For more iterating functions support queries to review see here….. - forum.enterprisedna.co/search...
***** Learning Power BI? *****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-reference-guide-download
FREE - Power BI Resources - enterprisedna.co/power-bi-resources
Enterprise DNA Membership - enterprisedna.co/membership
Enterprise DNA Online - portal.enterprisedna.co/
Enterprise DNA Events - enterprisedna.co/enterprise-dna-events-page/
***** Related Links *****
Using Complex Logic Inside Iterating Functions - Advanced DAX - czcams.com/video/1i1OlCpMVr0/video.html
How To Use Iterating Functions To Solve Total Issues In Power BI - DAX Concepts - czcams.com/video/QkFlY9bEotI/video.html
Advanced Logic Within DAX Iterating Functions - Detailed Example - blog.enterprisedna.co/2017/12/27/advanced-logic-within-dax-iterating-functions-detailed-example/
***** Related Course Modules *****
Mastering DAX Calculations - portal.enterprisedna.co/p/mastering-dax-calculations
Solving Analytical Scenarios w/Power BI & DAX - portal.enterprisedna.co/p/solving-business-scenarios-with-power-bi-and-dax
Unique Analytical Scenarios - portal.enterprisedna.co/p/unique-analytical-scenarios
***** Related Support Forum Posts *****
Combining Aggregating & Iterating Functions To Calculate Median - forum.enterprisedna.co/t/combining-aggregating-iterating-functions-to-calculate-median/999
Creating Plan / Actual For Year & Months - forum.enterprisedna.co/t/creating-plan-actual-for-year-months/2689/6
Calculating Average And Using DateMonth - forum.enterprisedna.co/t/calculating-average-and-using-datemonth/3013/2
For more iterating functions support queries to review see here….. - forum.enterprisedna.co/search?q=iterating%20functions
Virtual tables + Iterators = DAX - Power !!! Thanks Sam and Thumbs Up!
I've been working with real data while following some Pluralsight courses. Granted, I've only completed two of their beginner Power BI courses... but I've gained more practical skill from two of your 10-15 min DAX videos than both 4-5 hr courses I've viewed so far!
HUGE Thumbs Up + a sub... I'll definitely be seeking out more of your content. Great job, thanks!
Great video guys.
Question : In the later examples you iterated over locations instead of the sales table. Wouldn't every sale have a location? If so, how would iterating over location differ from iterating over sales?
Hi Neil, for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/search?q=Iterating%20Sales
great question. I have abolutely no idea what is the logic behind this. Can't find the answer. Really frustrating. And why the results are so different? As You wrote, every sale and customer has its location, so why reults are different. Why t oiterate other table?
This is a really good understanding of how data analysis in business is working
Thanks a lot
Hi, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: czcams.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
great video
Thanks Abrar, please don't forget to share the video and subscribe to our channel to be updated with all our upcoming videos.
So what’s the point of you sumx, related, etc. when 😮when you can just do that with sumx sales Max sales in total sales?
Sir, How to do summation of first 3 numbers in a series in power bi....?
Series: {5, 7, 2,3,9,10,2}
Desired sum: 5+7+2
Hi Antip,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well, it can be better handled using the Query Editor rather than applying DAX based solution. Paste the below provided M Code onto the Blank Query inside the Query Editor and by following the same steps you'll be able to achieve the results.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYrJDQAQEABb2XjPg7WuWkQlonci4jlH724GlIiRlhtcjiQK7bGeJpmKBP8XRYyMVK4cGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","{","",Replacer.ReplaceText,{"Text"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"Text"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Text", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5", "Text.6"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", Int64.Type}, {"Text.2", Int64.Type}, {"Text.3", Int64.Type}, {"Text.4", Int64.Type}, {"Text.5", Int64.Type}, {"Text.6", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Text.1] + [Text.2] + [Text.3])
in
#"Added Custom"
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful. Also, make sure that you're subscribed to our CZcams channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
czcams.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
would be good if we saw the table to understand better