The next step is to create the Market Basket table. ( Please note that you should create a calculation for Margin %, I am using this field for demonstration purposes only ) Sales = Sales * RELATED ( ‘Product' )Ĭost = Sales * RELATED ( ‘Product' )
While we are in the Sales table, we can add Sales, Cost, Margin and Margin% fields to it so we can do sales and margin analysis of individual products. Now our Sales table will look like the image below (please note that records with the same Transaction ID have the same value for the Market Basket) The code to add the Market Basket field to the Sales table is below:ĬONCATENATEX ( Sales, Sales, “-“ ) , We will identify a market basket as a string of all Product IDs in that basket separated by “-” character. To do that, we need to create a new field on the Sales table. The first order of business is to assign our sales transactions into Market Baskets. The product table has the Price and Cost information about the product that we will use to analyze size and profitability of our Market BasketsĪfter we have the two tables loaded and related by Product ID, we can start enhancing the model. The Sales table has every customer transaction, what products where part of the transaction and also quantity sold. Let’s walk through the data first… Initially, we have two tables in our model, Sales and Product. Please download the Zip file with the Market Basket Analysis model here. Well, I try to deliver on my promises even though it might take me two years to get there. At the end of that post I promised to publish a tutorial on how one might do Market Basket analysis using this function in Power BI.
It’s been almost two years since I posted about the CONCATENATEX() DAX function here. EDIT: – An updated Power BI Desktop File and a video on this topic can be found here:
0 kommentar(er)
