Create Cumulative Totals, No Date, No Index - Advanced DAX Techniques in Power BI
ฝัง
- เผยแพร่เมื่อ 15 ต.ค. 2024
- This is another great example of how advanced you can get with DAX formulas in Power BI. I showcase here how to create Cumulative Totals in Power BI, but not using any dates.
This is advanced DAX.....
I spend some time detailing the steps that you need to work through to complete this in Power BI. There are a few but when done together they enable a very unique and powerful insights.
There are many custom applications for this type of analysis. Good luck implementing this in your own models.
SUPPORT FORUM LINK - forum.enterpri...
**** Learning Power BI? ****
All Enterprise DNA TV Resources - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - Power BI Resources - enterprisedna.c...
Learn more about Enterprise DNA - www.enterprised...
Enterprise DNA Membership - enterprisedna....
Great video. An alternative solution which does not require ranking at all:
Cumulative total=
SUMX(
FILTER(
SUMMARIZE(
ALLSELECTED(product),
product [ product name ],
"SALESinsideSummarize" , [total Sales],
)
[SALESinsideSummarize]>=CALCULATE ( [total Sales] , VALUES (product , [ product name] ) )
)
[total sales]
)
I tried this but I get an error: Syntax for '[DefectSummarize] is incorrect... not sure what I did wrong?
test cumm =
SUMX(
FILTER(
SUMMARIZE
(ALLSELECTED(NCM_Cost_of_Defects_UOM),
NCM_Cost_of_Defects_UOM[Defect Type],
"DefectSummarize", [QTY_SCRAP],
)
[DefectSummarize]>=CALCULATE([QTY_SCRAP], VALUES(NCM_Cost_of_Defects_UOM[Defect Type]))
)
[QTY_SCRAP],
)
@@nancmadi I think that the comma after [QTY_SCRAP] is in the wrong place, it should be after the ")". Also, I think the comma after the second [QTY_SCRAP]shouldn't be there at all. Hope this helps.
@@nancmadi . hello, did you reach your target ? does it works ?
From my side not :-(
thank you for your feed back
Thanks, it's still work now. The code above have some mistake about commas but it deliver the great idea.
Dave
do you still have the pbix file I'm still struggeling with the measure thankx Roger
This is SO helpful! It should solve a request I've received that essentially asks for an 80 / 20, Pareto analysis.
This is so helpful after 5 years of uploading. Appreciate it so much. Could you help us a little bit more on a cumulative percentage, please?
Absloutely awesome logic !!! Thanks Sam !! This kind of stuff lifts average DAX users to a different level.... Thumbs UP !!!
Hello Adam,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
We've created a dedicated course which is available onto our Enterprise DNA education portal which addresses the topics pertaining to the "DAX Optimizations". You can check out our course. Or you can also post your query onto our Community Forum under the section - "DAX Optimization" where our expert will look into your data model and provide the results. Below is the link of the course as well as community forum provided for the reference.
Hoping you find this useful. Subscribe to our TH-cam 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.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
app.enterprisedna.co/app/player-course/12
app.enterprisedna.co/app/player-course/23
forum.enterprisedna.co/t/introducing-a-new-enterprise-dna-initiative-optimization-lab/14821
Sam, the product name measure might return blank if the Filter function returns 2 names with same sales amount.
Hello, I have to say thank you in the first place. The solution is great and like you've said, DAX is really powerful.
I have a question about performance - I've got a lot of products (approx 790k) and I am running direct query. There is an error with not enough memory, even though I have a solid PC with enough RAM.
Is there a way to optimize performance in this case?
Thank you!
Hello Adam,
Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
We've created a dedicated course which is available onto our Enterprise DNA education portal which addresses the topics pertaining to the "DAX Optimizations". You can check out our course. Or you can also post your query onto our Community Forum under the section - "DAX Optimization" where our expert will look into your data model and provide the results. Below is the link of the course as well as community forum provided for the reference.
Hoping you find this useful. Subscribe to our TH-cam 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.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
app.enterprisedna.co/app/player-course/12
app.enterprisedna.co/app/player-course/23
forum.enterprisedna.co/t/introducing-a-new-enterprise-dna-initiative-optimization-lab/14821
Good video , how about you have so many slicers, like Regions , Districts, and Dates, How will u go about it
This approach finally helped me solve my running total based on ranked sales per organization. I was finally able to segment my orgnazations into Top 50% of Revenue, mid 30% of Revenue and Bottom 20% of Revenue. The challenge is that I also want to use these measures in cards and with other measures... ie. what's the average deal value of companies in the lowest 20%. It seems like this strategy is only good for referring to lists. Where would I start crunching this down to card level presentations?
Hello Bill,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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.
Hoping you find this useful. Also, make sure that you're subscribed to our TH-cam 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/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
This is truly amazing, thank you. I do have two questions...
GREAT! This really blew my mind! Many thanks Sam!
Really nice tutorial!
To make this kind of ranking is necessary to have a table with the totals of each product?
For the case in which you do not have those totals, what formula can I use?
Thanks!
For help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/
Thank you,
Got my head all spanned :-)
Will have to work it out slowwwwly...
Running total , the last formula .. the cumulative total . I used Calculate and it is much simpler than Sumx , using iterative .. it is better for performance to use CALCULATE
The technique does not work correctly if they have several equal sales, because it assigns the same rank to several products.
therefore it is necessary to create an alternate index to tie the tie.
Grettings From Mexico.
Hi Sam, I worked through this formula and setup, but at the end, my cumulative column is returning the cumulative value, but only in the first row-ie it is not iterating throughout the table. Any idea why?
thx i want to do this and is the only post i find about it !!!!
Hi @vishiousvish2832, we're glad you found this video helpful! If you haven't yet, you can subscribe to our channel to see all our upcoming data skills and AI tutorials, and announcements. Cheers!
Awesome! great video. Can you please tell me how to restrict and display only the required number of "Ranking index" apart from adjusting the 200 max list. I'm want to view the ranking only for the number of records my report display. If I filter my data based on month or quarter the ranking should adjust based on that.
Hello Mohammed Hadi,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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.
Hoping you find this useful! You can subscribe to our TH-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
I tried to reproduce the measures on a similar dataset ( generated random product# and random sales )
I spend some time but the measure always gave awrong result. The problem was the dubplicate product#.
In the table there were duplicates poduct# which causes the suggested measure did not work proprerly.
First you have to generate a virtual table with unique product#/sales within the measure to get the correct result .
I can sent the pBbix file if you're interested.
Thank you! Well done!
Indeed a very nice video. I was stuck up in this and found a way out with RANKX. Just one question would all of this work if we have same Sales of 2 Products!!? I had resolved that issue though.
Should do
@@EnterpriseDNA Hey Sam!, Could you please check this case scenario, there should be something to break the ties. Else we would get Blank. I wan to attach my file but it's not possible and I really appreciate your videos and your reply too!!!. Thanks a lot and keep posting great stuff. Really love watching your videos.
This is brilliant and so well explained. Thank you!
How it briliant? Can you use it with other things?
Hey Sam very interesting video! Just wanted to make sure I understood this though: So if I understand this correctly SUMMARIZE() works like ALL() where in this case it ignores the current evaluation context (the product name) on the table to give you a virtual table with a full list of product names? Just want to make sure I understood this correctly because that is extremely powerful! Thanks! - Alp
It doesn't really work like ALL, it still respects the current context.
You have to remember what the context is in this example. It's an index column that has no relationship to anything. So eventhough it is in the table, it is not really providing any context that would impact the SUMMARIZE virtual table.
I cannot get your solution to work on my example. Even though all rows are filled, it says
A single value for column ‘Gross Margin’ in table ‘Sales’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Why is that? As I said each row in column 'Gross Margin' is filled with a value (same with 'Product Name').
Hello Herr San,
Thank you for posting your query onto our channel.
Firstly, this error doesn't have to do anything with the records i.e. whether they're filled or blank. The thing is, you measure is not converting the results into a scalar value i.e. where only single value should have been generated in that place it's generating multiple values and therefore you're getting this error.
The video is created from an educational point of view, so your scenario might be different than the one showcased in the content. Also, it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
Therefore, we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner.
Hoping you find this useful. Make sure that you're subscribed to our TH-cam 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.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Hi Sam, thanks for the video! I run into issues but it may be because my approach is slightly different to yours. I have a rank column on my table that I store as a variable like below. The result of this column just returns the revenue for that particular customer rather than the cumulative. Any idea what I'm doing wrong here?
Revenue Running Total =
VAR clientRank = SELECTEDVALUE('Distinct Account Records'[Rank 3Y Revenue])
RETURN
SUMX( FILTER(
SUMMARIZE('Distinct Account Records','Distinct Account Records'[Ultimate Parent ID],
"Revenue", 'Distinct Account Records'[Ultimate Parent 3Y Revenue],
"Rev Ranking", RANKX(ALL('Distinct Account Records'), [Ultimate Parent 3Y Revenue], ,DESC, Dense)),
[Rev Ranking] < clientRank),
[Revenue] )
Hello Luke,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well, just based on the measure we cannot comment where the error is actually committed. It's always a difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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.
Hoping you find this useful. Also, make sure that you're subscribed to our TH-cam 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/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Sum, really amazing video, but I am freezed in a little problem... I do not want to continue showing the last product when I do not have more products in the list and also I want to show the Grand Total. I could resolve these two steps in the measure before the cummulative, but I have tried a few times to do the same ticks in the "Cummulative measure", but failed. So, how could I complement the "Cummulative measure" to stop showing last product amount where I do not have more products and also show the Grand Total? Could you please give us some light?
Would need to see what you're looking at here I think. Bit too much at play with a nmber of variables that could adjust the formula. See here for more personalized support. - forum.enterprisedna.co/
Is there a way to calculate the percent each entry is of the total cumulative amount? And, can this be modified to utilize slicers to limit which groups are to be included?
Hello Gil Garnsey,
thank you for posting your query onto our channel.
Well your question is not clear to us and we're not able to understand what you're trying to achieve here. It's always a little bit difficult to judge and provide the results without looking at the data structure, model, working of the PBIX file for the reference as well as the results that you're trying to achieve.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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.
Hoping you find this useful.
Happy Learning!!!
forum.enterprisedna.co/
Hi Sam, What will happen if there is a tie in the RankX function in the 'Cumulative Product Sales' measure?
Hello Arjun,
Thank You for posting your query onto our channel.
We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA TH-cam channel pertaining to this topic where how you can break ties.
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 and meets your requirements that you've been looking for. Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
blog.enterprisedna.co/rankx-in-power-bi-developing-custom-tiebreakers/
th-cam.com/video/HkR1JVzmvRw/w-d-xo.html
th-cam.com/video/1givosMLhCI/w-d-xo.html
Awesome! How would you handle data where different Product Name entries had the *same* Total Sales value? My data has this feature, thus the first step produces an output which doesn't return these Product Names. Any ideas?
You see here where it says ties - RANKX(, [, [, [, ]]])
Try placing DENSE in that parameter and see what you get.
Even after placing dense we will have more than one product at same rank
tried the formula for ranking index but the result is different
Can you help... I know this was recorded last year...
I keep getting a syntax error for [scrap ranking] is incorrect : or Parameter is not correct type on the SUMMARIZE
CUM_SCRAP =
VAR IndexRank = [Ranking Defect Value]
RETURN
SUMX(FILTER(
SUMMARIZE( NCM_Cost_of_Defects_UOM, NCM_Cost_of_Defects_UOM[Defect Type],
"scrap", [TOTAL SCRAP],
"scrap ranking", RANKX(ALL(NCM_Cost_of_Defects_UOM[Defect Type]),[TOTAL SCRAP], , DESC))
[scrap ranking]
Try this :
CALCULATE (
[total scrap],
FILTER (
ALL (NCM_Cost_Of_Defects_UOM [DEFECT TYPE] ),
[Ranking Defect Value] < = MAXX (VALUES ( NCM_Cost_Of_Defects_UOM [DEFECT TYPE] ),[Ranking Defect Values ] ) )
)
)
Acumulado =
var serie = max (tabla1 [N] )
var resumen = filter ( allselected ( tabla1) , tabla1 [N] < = serie )
return
sumx ( resumen , [campo montos] )
Many thanks🌹🌹🌹
Hi Masoud, glad that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Hmm i thougt it was perfect but suddenly i got blanc lines between a every couple lines very weird, he just does not show every line as it Should Any idea why?
Hello Ronald Postelmans,
thank You for posting your query onto our channel. Well we're not sure how you're getting the results as blank lines. You can raise your query onto our Community Forum so that members as well as experts team can assist you in a better and efficient manner. Hoping you find this useful and meets your requirements that you're looking for. Happy Learning!!!
forum.enterprisedna.co/
How did u calculate [Total Sales]?
Can you tell the metric u used for it?
Hello flower Queen,
thank you for posting your query onto our channel.
Well you can calculate the "Total Sales" by two ways. Both the ways are provided below for the reference -
1). Total Sales -
Total Sales = SUM( Sales[Revenue] )
2). Total Sales -
Total Sales =
SUMX(
Sales ,
Sales[Price] * Sales[Quantity] )
Note: First formula is useful when you already have a column in your Sales table with the Revenue records in it whereas second formula is useful when you don't have Revenue records in your Sales table and only have Price and Quantity records and based on that you've to calculate the "Total Sales" figures.
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 and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/
Hi Sam
I tried to write a measure for the cumulative sales and it's work, but I don't understand why :).
can you please look on it?
Cumulative Sales By Ranking = CALCULATE([Total Sales],Filter(All('Product'[Product ID]),Rankx('Product',[Total Sales],,DESC)
Hard to judge without seeing the results and data on this one. Check out forum.enterprisedna.co/ if you require some additional help on it. Chrs
I would like to know how do you create the tables o load your information. I'm trying to do something like that but it doesn't work. I hope you can help me.
See here - th-cam.com/video/HDsncpjpM3E/w-d-xo.html
Dude he unncessarily makes things complicated. This result can be achieved by making a simple helper table rather than using his ways. This video is a great example of how not to use DAX.
interesting...but what would happen if you sort the table by product name? (instead of sorted high to low)
You would just need to use and index value instead
OMG... Hope I can replicate it.
Difficult but works perfect
Hi Ronald Postelmans, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
This is super!
Thanks! Please watch out for our 30 for 30 this month, that's 30 videos for 30 days! Don't forget to subscribe to our channel :)
Awesome! Thx
Hi Sepideh Zamnai, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
OOOOOOOOOOOOOOOOOOOOOOOOOOOOOO My God.
Is there any simple way ?
How can i use it with other parametrs?
Is the PowerBI so difucalt thing?
I do it BUT How then?
Hello Saburov Nariman,
Thank You for posting your query onto our channel.
Well, we didn't understand your query here pertaining to the - "How can i use it with other parameters?". We're not sure what's your requirement here and the results that you're trying to achieve. It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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.
Hoping you find this useful and helpful. Also, make sure that you're subscribed to our TH-cam 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/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
are you running these over LARGE data sets? Performance is horrible.
Well yes there's a lot more logic to run through so performance will likely be slower on larger datasets
This is my current problem. I have 30k products and the fact sheet sales has 15 million re registrations. I need to find a way to calculate the accumulated dynamically. :(
@@JuniorMartinsJP can you tell me if you can calculate the running total? I have the same issue with morathan 10 millons of rows ;(
I can't believe how complicated this has to be to do a RTotal....grrr. plus you would need to remove all the blanks that occurred as shown here: th-cam.com/video/f5k4fD1YJrQ/w-d-xo.htmlm41s