Cumulative Totals Deep Dive - Power BI & DAX Formula Review

[@EnterpriseDNA]

***** Related Links ***** Running Totals In Power BI: How To Calculate Using DAX Formula - blog.enterprisedna.co/running-totals-example-in-power-bi-using-dax-formula/ Showcasing Budgets In Power BI - DAX Cumulative Totals - blog.enterprisedna.co/showcasing-budgets-in-power-bi-dax-cumulative-totals/ Cumulative Totals Based On Monthly Average Results In Power BI - blog.enterprisedna.co/showcasing-budgets-in-power-bi-dax-cumulative-totals/ ***** Related Course Modules ***** Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-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 ***** Cumulative Total Question - forum.enterprisedna.co/t/cumulative-total-question/1054 Why Is My Running Total Showing Values After A Specific Date? - forum.enterprisedna.co/t/why-is-my-running-total-showing-values-after-specific-date/6346 Running Totals On A Measure With No Index Or Date Column - forum.enterprisedna.co/t/running-totals-on-a-measure-with-no-index-or-date-column/147 For more running total support queries to review see here - forum.enterprisedna.co/search?q=running%20total

[@ajitesh_thakur]

Pls see my question :) - Why Does the ALLSELECTED not remove the Row context for MAX() function?

[@finisterremork]

Thanks Sam, I have been thrown in the deep end with power BI at work and your vids have made me look way smarter than I really am. Cheers!

[@MadridG]

Thank you very much! I'm Brazilian and there isn't enough content in Portuguese. You helped me so much.

[@jabrillo]

Can you do a video on reverse cumulative totals?

[@EnterpriseDNA]

Hi jabrillo, appreciate the suggestion. We'll include this in our list of possible topics for our future videos!

[@jasonrymer7973]

Thanks for sharing Sam, great video clears up an issue I was having analysing construction programs.

[@EnterpriseDNA]

Hi Jason, we're glad this video is of help. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@neilruttley1266]

This is really smart. I have a cumulative report in excel that I want to put into power BI instead. You are looking at total sales but how can i change the interactive graph to break down the cumulative sales by a particular region or sales person for example?

[@EnterpriseDNA]

Hi, thanks! There are several videos showing how this can be done. Just like this video for example studio.kzbin.info0lgMjLj_TeU/edit Alternatively, browse through the forum posts on determining sales by region here: forum.enterprisedna.co/search?q=by%20region

[@ajitesh_thakur]

Why Does the ALLSELECTED not remove the Row context for MAX() function?

[@Iasdsfdsdaerbrt]

the same question

[@zxccxz164]

nice but how do you do a cumulative total in dax based on a category?? (NON Date or number) The MAX value gets an error on the text field

[@EnterpriseDNA]

Please see here - kzbin.info/www/bejne/nGbOZZl6Zr59qLM&lc=UgzetEqAVClZOxrnl4B4AaABAg

[@tanababa]

Great video Thank you. How would I go about if I would like to have the Running Totals based on the Values shown in the table (and not from the original data table)? I have months as the first column and a numeric score measure on the second column. The score is a complex formula. I need to show how the score evaluates from month to month by adding each score to all its previous scores. Thank you, Tamir

[@EnterpriseDNA]

Check out the technique I showcase in this video here. Sub into the correct measure into the SUMX part of the formula. This should get you what you need.

[@jaikoshta4060]

Great Video, but it seems like its not working for me. I've also an coumn with the date when the issue are reported and then another column when the issue are closed and for that I would like to create the running total (i.e. on 06/21 --> 16 issue, 07/21 --> 61, 08/21 --> 21 and so on... accordingly I would like to have the running total but its seems I'm doing something wrong, can you please help me ? Regards,Jai

[@EnterpriseDNA]

Hello Jai, 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. Subscribe to our KZbin 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/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

[@hokki28]

Cumulating by hand would be done differently (and with less effort) just by adding the n-th element to the sum of the last n-1 elements. DAX is recalculating sums again and again for every date. Isn’t that inefficient? Can it be done like I would do it by hand or in Excel?

[@EnterpriseDNA]

Hello H. Hagedorn, Thank you for posting your query onto our channel. Well, firstly Power BI is not Excel wherein one can calculate or evaluate the results for each individual row. Excel works at a row level whereas Power BI works at columnar level. If one prefers to calculate it by hand or manually then one can make use of the Excel. And, no it's not calculating it again and again. When one uses "SUM()" function then in that case, first it does the aggregation of the entire column and then evaluates the results whereas in the case of "SUMX()" function it runs through each individual row and when new records are added it does the added evaluation for those rows. We recommend you to please go through our basic courses which are freely available onto our Enterprise DNA KZbin channel i.e. "Ultimate Beginner's Guide to Power BI" and "Ultimate Beginner's Guide to DAX". Also, if you want to learn about the Optimization of the DAX then we also have a course available onto our education portal. Below is the link provided for the reference. 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 KZbin 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/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ portal.enterprisedna.co/courses/enrolled/958368

[@BIMoji1]

Great Explanation. Thanks for making this video.

[@ulduzmammadova2288]

Hello! I kindly ask your help. Is it possible to do dynamic abc, xyz analysis in power bi? But i should use slicers for categories a,b,c. Pleasee help(

[@EnterpriseDNA]

Hello Ulduz Mammadova, thank you for posting your query onto our channel. Well, any sort of dynamic analysis is always possible in Power BI but 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 KZbin channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning!!! forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@dafran500]

Hello! Thanks for this video. I like how you explain formula per formula, it is very helpfull. However I am having an issue and sadly haven't found the solution. For example, when are two equal values on the sales, the cumulative measure stop adding and It repeats the same value until the valueis different. I am trying different ways to do this calculation but this keeps repeating this result. What can I do? :(

[@EnterpriseDNA]

Hello dafran500, Thank you for posting your query onto our channel. We really appreciate your query and 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 KZbin 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/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

[@devanshitandel435]

Thank you for the video but I have a que that in my case "MAx_date = MAX('Date'[Date])" is giving me the largest date of the column, if you can help.

[@EnterpriseDNA]

Hello Devanshi Tandel, thank You for posting your query onto our channel. Well the formula is working absolutely fine in our model i.e. MAX( Dates[Date] ). It's always a little bit difficult for us to judge and provide the results without looking at the working of the PBIX file. The only suggestion as of now which we can provide it to you is that please check the relationship ststus between each and every models because these can happen if there's an error performed while establishing the relstionships between the tables. For furthermore queries, you can reach out to us onto our Community Forum where our members well as experts team will be able to assist you in a better and efficient manner. We're providing a link below of our forum for the reference. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/

[@gabinperret7599]

you saved my exam thanks 🙏

[@mathildeschenkel8146]

Thaaaaaanks 🙏🙏🙏🙏

[@daniyarberik9492]

Thank you so much. Great explanation!

[@EnterpriseDNA]

Hi Daniyar, we're glad you appreciated the video! You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@NirRobinson]

Very good explanation! Thanks.

[@knudknudsen1188]

This doesn't work if there is no value in total sales for a specifik date - in that case it displays an empty cumulative revenue for that date. How do handle empty values?

[@EnterpriseDNA]

Hello Knud Knudsen, thank you for posting your query onto our channel. Well we're not sure what you mean by "Empty Cumulative Revenue" but we're providing a formula below where you can prevent it from further calculating where you don't have records for future dates. Below is the formula provided for the reference - Calculation of Total Cumulative Revenue Till Specific Date - Cumulative Revenue = IF( ISBLANK( [Total Revenue] ) , BLANK() , CALCULATE( [Total Revenue] , FILTER( ALLSELECTED( Dates) , Dates[Date]

[@asthatripathi9211]

SUM (COUNT(app_id)) OVER (PARTITION BY date, Type,categories ORDER BY Months DESC) AS RunningAppCount Total how do i achieve this sql query in power bi?

[@janacar3162]

Thank you so much! you guys are the best! S2

[@Kieronmullan]

Great video, but please SLOW DOWN, some of us need to follow what your doing by repeating it in our PBI examples to build knowledge. Cheers

[@salvatorescofano6756]

Very good !

[@souravsen8861]

what is the data you want to cumulate is not a measure and a column

[@EnterpriseDNA]

My advice is get it into a measure. This is the best way to run any calculations in Power BI using DAX

[@Mrwoudney]

Thank you very much!

[@professordrabhijitsayamber2299]

Om shanti k good day please

[@peanut8162]

pls dont whisper

[@Wzxxx]

Thank You.I really don't understand what ALL does in this example. As when You don't use ALL, there is still filter (Take all dates before the date in a row and sum sales),so for my logic it should still sum all dates up to particular date in a row. Why there is a need to clear year and quarter filter (with ALLSELECTED or ALL) if it does not influence the date in a row. So for my logic if there is a date in a row 1.1.2020, the formula takes all dates up to 1.1.2020 and SUM. I do not understand the logic behind "take ALL dates'

[@EnterpriseDNA]

Hello Wzx, Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post it. 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 KZbin channel pertaining to this topic. 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 KZbin 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/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ community.powerbi.com/t5/Community-Blog/What-s-the-Main-Difference-Between-ALL-amp-ALLSELECTED-DAX/ba-p/1339726 blog.enterprisedna.co/using-allselected-dax-formula-concepts-in-power-bi/ kzbin.info/www/bejne/qGKkk3apgNytsNU kzbin.info/www/bejne/bZu3gmOJobR9fpY kzbin.info/www/bejne/Z3zJhaCIjrd_kNU