Calculate de number of combinations of items orders

[EmanuelTavares]

Hi folks, I need help.

I am developing a dashboard with the goal to show the number and the frequency of pair of combinations of items in orders. After a couple of days, I'm stuck and I decided to put this question here and ask for help.

The data model is simple, we have a table Products, Orders, and Items and my first action were to filter to have just the order with more than one item.

So I tried unsuccessfully to obtain these numbers using just the table items.

Then I decided to create a copy of table Items that I called Combinations, to be the "second product" on combinations.

The database design looked like this:

The idea is: for each product in Products, I look for the Items and found the orders, then I related to orders and finally to Combinations to have "the other product".

To calculate the total of the orders I created a measure:

TotalOrders = CALCULATE(DISTINCTCOUNT(ORDERS[order_id]), ALLCROSSFILTERED('ORDERS'))

It worked very well. The totals of products, orders, etc are fine too.

But I need a measure that considers the created_at to analyze periods but ignores the other filters, and that is where I stopped.

I'm not a DAX expert, and I tried to elaborate formulas using ALL, ALLSELECTED, ALLEXCEPT, ... and nothing worked.

I need to know the total of orders in a certain period of time to know which combination was more frequent to use as a base of the calculation of the frequency in percentage.

When I use the total without filters I obtain the percentage comparing with the total of the orders.

I have particular problems in the table visualization because I need to put these 4 columns: Product 1, Product 2, Total of Orders, Frequency, and the products are interfering in the Frequency calculation, applying a filter in the total of orders.

I hope that my explanation was clear enough.

There is someone that could help me?

Best regards,

Emanuel

[Anonymous]

Hi @EmanuelTavares 

Can you explain it with specific data? For example, what does your source data look like? What is the result you want to achieve? If possible, can you provide your pbix file (delete sensitive information) ?It will help us handle the problem easier .

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.