form-8850.com is not affiliated with IRS

Welcome to exhale magic trick number 1398 hey if you want to download this excel workbook EXO nice trick 1398 finished or the start file so you could follow along click on the link below the video hey we got a great video here where we're going to talk about dax formulas for running total and % a running total and we'll see a few other cool Dax formula tricks now the last couple videos have been all about running totals we saw how to do running totals and % running totals with pivot tables and then we saw three different videos about formulas but in this video we want to see how to do it with Dax warn us now here's our first data model pivot table using Dax formulas and we need to create the categories with a lower and upper limit 1 2 50 51 to 100 and notice the increment for each one of these is the same each increment between lower and upper is exactly 50 then we need Dax formulas to count so there was a hundred and three transactions that had quantity between 101 and 150 then we need our running total so that's cumulative running total through all three categories 173 that means there were 173 transactions that had quantities of 150 or less and we need our percent running total or percent cumulative total so 42 percent of our transactions had quantity of 150 or less now our second data model pivot table will be based on month and notice for any particular row here we're going to calculate total quantity which is different we're going to be adding the quantity up here we were counting but we're going to add then calculate a cumulative total and percent cumulative but notice there's still a lower and upper limit here beginning of April as the lower limit end of April is the upper limit both of these pivot tables have increments are the same this is a month this is 50 then our third example we'll see how to calculate categories where the increments are not the same we have an increment of 75 75 50 and then 100 and in this data model pivot table will actually count transactions based on the quantity column and then we'll add total quantity for each category all right now we're going to close this finished file and we're going to go to the start file and I'm on the sheet tables this is the quantity column in the fact table now each one of these tables are in Excel and I've already converted them to a table and named them so if I go up to design the name of the fact table is f quantity now some of these tables are already imported into the data model we'll go look in a second here's our product table customer table date table five table and here are the two category tables now we'll come back and look at these category tables in just a moment but I want to go look in the data model because I've already imported the first four table I'm going to power pivot over to data model we could see down here I have F quantity D product D customer de date now we come up to diagram view and the relationships already established here's our fact table these are all lookup or dimension tables customers there's a unique list of customers that's the one side over to the many side we can sell many transactions to each customer the date or calendar table that's a unique list of dates that span the full range of dates in our fact table one of each date unique list that's the one side over to the many we can sell many transactions for a given date here's our product table a unique list of products we have the one side over to the many side because we can sell that product many time now we want to import our first category table then build a calculated of them that will allow us to build a relationship between the fact table and our first category table alt-tab now I'm going to click in this table I've already converted it to a table and very carefully named it it's D regular increments later in the video when we use this table we will import it and it will be de regular increments we'll also deal with this table in a really cool way in just a little bit now let's click in this table go up to power pivot there's our add to data model so we need to build this calculated column so we can build a relationship but I want to go over to data view and look notice here is our D regular increments table those are the categories we'd like to you there's the lower and upper now in order to build a relationship this is a dimension or lookup table so I have to have a unique list and I do there are the upper limits now I want to go look at the F quantity drag this down a bit here's our quantities now I can't build a direct relationship between this table and the D regular increments because in PowerPivot there's not an approximate match lookup like there is in Excel no problem I want the upper limits listed as a calculated column and I can do that in a simple way because the increments are the same notice 74 174 325 Oh what if I rounded all these up to the nearest 50 that would give us our calculate a column that we could build a relationship upon now I'm going to double click the column header and name this irregular increments and enter now the formula we're going to use its equal sign that jumps me up to the formula bar is we're

Loading, please wait...