topic Re: Basket Analysis Both Items Total Sales in DAX Commands and Tips

Basket Analysis Both Items Total Sales

MAAbdullah_47 — Fri, 14 Aug 2020 10:39:09 GMT

Dear Members,

I Created Items basket table from python script named (Basket Analysis) through power query successfully to calculate (support,lift and confidence) this table display the basket of each two items and measure the strength of relation through (support,lift and confidence) columns.
The table is Joined with Products table through the primary key (Product No) 1:m and of course products related to sales order (1:m),

The challange is I need to calculate the total sales of Both Items in the basket table (A,B) in all orders from table (sales) considering there is no relationship between (Basket analysis) and (Sales) Table, I created one measure to do this but it is not do it correctly :

M2M Total Sales =
CALCULATE(
[Total Sales],
FILTER(
Products,
CALCULATE( COUNTROWS( 'Basket Analysis') ) > 0
)
)
I appreciate your help.

Note: Here is the Link of all resources.
https://drive.google.com/drive/folders/1Voi-Wr3IvbTptVbVui6VDQ-llIohZTbb?usp=sharing

Re: Basket Analysis Both Items Total Sales

Anonymous — Fri, 14 Aug 2020 11:56:43 GMT

I can't see the primary key (Product No) in the picture. I also can't see all the relationships to Basket Analysis (if there are any more than the ones shown). To do what you want, you have to have 2 relationships from Products to Basket Analysis: one to the first product, the other one to the other. One of them will be inactive. To calculate the amount for the first product you'll do nothing more than just return the sales amount [Total Sales]. For the other product, you'll need to use the USERELATIONSHIP function with some manipulation of filters on Products and Basket Analysis. This is not hard but you have to understand exactly what you want to do and remove the original filters.

Re: Basket Analysis Both Items Total Sales

MAAbdullah_47 — Fri, 14 Aug 2020 16:08:43 GMT

Anonymous Sorry The Primary Key is (Index) in Products table.

Re: Basket Analysis Both Items Total Sales

MAAbdullah_47 — Fri, 14 Aug 2020 16:18:51 GMT

Anonymous Target Itme P No is the foriegin key in (Basket Analysis) , I include the resources in the thread, For the second Item (Second Item P No) , For the Measure I Include it in the Thread, Cold you help me on what sort of Modification shall I do?

Re: Basket Analysis Both Items Total Sales

MAAbdullah_47 — Sat, 15 Aug 2020 13:59:37 GMT

Hi Anonymous

You comment: "you have to have 2 relationships from Products to Basket Analysis: one to the first product, the other one to the other. One of them will be inactive."

I did the Active Relationship BTW (Index from Products into Target Item P No from Basket analysis),

For the Inactive Relationship BTW (Index from Products into Second Item P No from Basket analysis).

I need your help to show me in the second (Inactive key) how to Use (USERELATIONSHIP ) with sales (Product forign key) on the absence of any relationship between both tables (Sales and Basket Analysis), (Basket Analysis Doesn't have primary key).

Thank you.