The CALCULATEfunction was used to call on Total Profits but in the context of a modified filter. The formula generated context to relate the supporting table to Total Profits. Running the advanced DAX function and inserting the measure into your table will give you the Total Profits of each customer group. I’m going to show you the entire formula for Customer Group Profits first and then I’ll explain what each line does. While it is complicated, using advanced DAX functions in Power BI will make this achievable. To fix this, I’m going to create a new measure and write an advanced formula. Since there is no relationship between the two, you’ll see that the Total Profits of $5.01 million goes across every single customer group. With all that set up, I’m going to bring in my Total Profits measure inside the Customer Groups table. I went ahead and transformed my Total Sales into a card. I like to do this because the data will make more sense later on.īefore proceeding, I’m going to make some visualizations. Go to the column tools of the Customer Groups table and sort the Groups column according to the minimum ranking. To start, I’m going to sort my supporting table. This step will require advanced DAX functions in Power BI. The goal is to run logic through this table based on the ranking of my customers. In this example, I deleted irrelevant measures and dragged my supporting table to the bottom.Īs you can see, it has no relationship with my core data model. Whenever you create a new table in Power BI, it’s best to check and organize your data model so you have an overview of how everything is connected. Indicate the minimum and maximum ranks of each group as shown below. Name the supporting table “Customer Groups” and add columns ‘Group’, ‘Min’, and ‘Max’.Īfterward, group the clients if they belong to the Top 50, Rank 50-200, and The Rest. This will enable me to create dynamic groupings based on custom logic. Creating A Supporting TableĪfter setting up my basic measures, I’m going to create a supporting table. The next step is to identify who in this time selection are the top clients, the mid-ranged customers, and the rest. You now have Total Sales, Total Costs, and Total Profits. Select ‘Currency’ in the format drop down menu or click the currency symbol. I also like to fix the format right away just so my data is organized. Now I’m going to drag the measures for Total Costs and Total Profits into my table. This methodology is a far more effective way to do complex calculations and it will save you a lot of time. This is the beauty of measure branching: you can reference measures within measures rather than having to rewrite advanced formulas repeatedly. Create a new measure and subtract Total Costs from Total Sales.Īs you may have noticed, you don’t have to reference anything in the table anymore. Hit enter and wait for the calculation to appear on your Key Measures.įrom here, you can branch out easily to Total Profits. You will need to use the RELATEDfunction to call on another table, particularly the Products Cost table, to achieve this. To get the cost, you need to use the SUMX function, which returns the sum of an expression evaluated for each row in a table.Īdd the Sales table and multiply each sales quantity to the corresponding product cost. I’m going to start by creating a new measure and renaming it as Total Costs.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |