Calculating Sales Amount by Product Category in DAX and Power Query

In this video, I'm going to show you how to use the general user interface of Power Query

of Excel to calculate the total sales by product category. We're going to switch over to the Power BI data model and write the DAX formula to achieve the

same result. So let's get started. Now, these are all our tables loaded into the Power Query editor

This is going to be the fact table and of course we have a couple of dimension tables

Now we want to work with the fact table and the dim product table

We want to be able to access the pro category Now to do that we're going to create what's called a snowflake margin

So I can right click at this space here and choose new query

And then click on combine and choose merge queries And then we're going to see the merge dialog box

Now we need to select the tables and the matching columns to create a merged table, in other

words, to create the snowflake margin. Now I'm going to select the fact table, and then I'm going to choose the dim product table

because we want to focus on total sales by product category. Now we can see the product category in the dim product table

Now we can see the product key is associated with the product key in the dim product table

So now for the join kind, we're going to choose the inner with only the matching rows returned

And then we're going to see the selection matches 49,999 of 49,999 rows from the first table and so on

So go ahead and click OK. Now we can see we have the matched query as new

I going to rename this I going to call it sales amount by product category so I going to select everything control C to copy control enter or click enter to commit alright so I going to scroll to

the right and of course you can see this dim product table now want to access the

product category column in this dim product so I'm going to click on this

expandable button and uncheck use original column name as prefix and I'm

to uncheck all the columns now i won't actually pull across the product category because i want

to do total sales by product category so i'm going to click on okay so you can see the product

category now included into the fact table of the matched table which is fine then let's go ahead

and use the general user interface now to do that i'm going to come to the transform tab under the

table group i'm going to choose group by and of course you can see the group by dialog box now

because the product category column is selected i can see that's already populated for me as the

column to go by and then i can just give name for the new column i'm going to control v instead

sales amount but product category now for the operation i want to perform the sum i'm going to

choose the sum and then for the column that i want to aggregate on i'm going to choose the sales

amount okay and then i'm going to click okay amazing so we can see the output so you can see

the total sales amount by product and that's quite amazing. I'm going to fix this in the currency data type and then I can come to the home tab

close and load to. Now we don't want to dump all the tables, we just want to create a connection

So choose only create connection and go ahead and click on OK

And I'm going to scroll down. We have the sales amount by product categories we can right click and choose load to and then I can load to a table in the existing sheet and go ahead and click okay all right amazing so let apply currency formatting

to make it more visible let's type in the total here i'm going to come to the home tab click on

the styles sales styles click on the total and i'm going to do the alt equal and that's perform the

other sum and then i can type in the total here so we can see the total sales amount for the product

category is 75 million 357 thousand nine pounds now let's go to the power bi data model and achieve

the same thing again we have all the same tables loaded the fact table and the dimension table

now we just need to write the dax measure so i'm going to right click and choose new measure

we calculate the sales amount by product category now we're going to use the amazing calculate

dax function which is the attribute of performing dax or writing dax formula within the power of

the data model now just like you know the calculate evaluate an expression in a context that is

modified by filters okay you can use the sum aggregate function and then i can call the

the sales amount column that is present in the fact table. So sales amount, right

And then I'm gonna close that up. Now, comma. So for the filter

so I'm gonna actually use the filter function. I wanna filter the fact table, comma

Now for the filter expression, what's the criteria? Now we'll filter the column where we have the product key

present in the fact table and the product key also present or that exists in the

the product table So I going to call the product key in the fact table here and then I gonna use the aim and use the values DAX formula and I want a point I

want to check whether that the simple key exists in the DIMM product column so

DIMM product so I'm gonna check that and close the brackets and of course we can

go ahead and commit this DAX formula so let's just click enter so you can see

our dax measure is created and we can apply currency to format nicely and let's use that

in our report now i'm going to look for the dean product that is the product category so i'm going

to grab the product category drop it in the report canvas so we can see the list of the product

category and then i can go ahead and grab the measure we just created sales amount by pro

category i'm going to drop it and of course this actually creates a chart the bar chart

i want to actually switch over to either a table or a matrix let's just use a matrix that's fine

so we can see this return 75 million 357 thousand nine or pounds or dollars we can change the

currency and when you check this total with what we achieve in excel you can see we have the same

out with 75 million 357 thousand nine pounds so let's come to the power bi and check again

75 million 357 thousand nine dollars per pounds so this is basically how we can use the power bi

to write a dax formula to calculate the total sales by product category and how we can use

excel general user interface to calculate the sales amount by product category so i hope you

enjoyed this video if you do comment like and share with your friends thank you and bye for now cheers