topic Re: Find Customer sales where customers bought few products together in DAX Commands and Tips

Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 04:40:14 GMT

I need to find customer sales only when customer buys all 3 products of Sofa, Chair, Table. Attaching the sample screenshort of the expected output , Please may I have help.

Re: Find Customer sales where customers bought few products together

Irwan — Wed, 14 Aug 2024 04:59:30 GMT

hello han_rj

please check if this accomodate your need.

the easiest way is to achieve this is filter out customer that has not bought 3 item.

1. create new measure to count bought item

Filter =
var _Count = COUNT('Table'[Column3])
Return
IF(
    _Count=3,
    1,
    0
)

2. in your matrix visual, insert the measure

3. in visual filter pane, set to show value only 1. value 1 means customer that has bought 3 items.

4. if you dont want to show those count value, lock the filter then remove the measure after being locked. the filter will be stayed there while no value is shown.

however, as you can see, the total will be different from original, because only customer with 3 item will be count in total.

Hope this will help.

Thank you.

Re: Find Customer sales where customers bought few products together

Jihwan_Kim — Wed, 14 Aug 2024 05:01:21 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

sales total: = VAR _sales = SUM ( fct_sales[sales] ) VAR _productcountall = COUNTROWS ( VALUES ( dim_product[product] ) ) VAR _productcountonlysales = COUNTROWS ( SUMMARIZE ( fct_sales, dim_product[product] ) ) RETURN SWITCH ( TRUE (), HASONEVALUE ( dim_customer[customer] ), IF ( _productcountonlysales = _productcountall, _sales ), _sales )

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 05:02:22 GMT

The above data is a sample data, I want specific condition on Sofa , Chair and Table , Because I have other items as well.

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 05:05:05 GMT

I will give this a try, the above attached data is a sample data and I would like to put a condition only to compute sales for 3 specifc products [Sofa , Chair and Table] scenario , else it should show zero. But I will give this a try , Thank you

Re: Find Customer sales where customers bought few products together

Jihwan_Kim — Wed, 14 Aug 2024 05:10:53 GMT

Hi,

My calculation has the condition to compare "how many products per customer" vs. "count of all products in dimension table".

If you need to compare to three specific products, please try hard-code the products names, instead of dynamically comparing to ALL.

Thank you.

Re: Find Customer sales where customers bought few products together

Irwan — Wed, 14 Aug 2024 05:10:33 GMT

hello han_rj

you can adjust the filter DAX just only to calculate those 3 items.

Filter =
var _Count = CALCULATE(COUNT('Table'[Column3]),FILTER('Table','Table'[Column2]="Chair"||'Table'[Column2]="Sofa"||'Table'[Column2]="Table"))
Return
IF(
    _Count=3,
    1,
    0
)

Thank you.

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 05:55:38 GMT

Hi Irwan , This works but when we export data and open the csv file it is blank

Power BI desktop Data :

Excel Data

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 05:58:58 GMT

Hi Jihwan_Kim , I did try filtering, but I guess I am missing something attaching the changes made and the data

Re: Find Customer sales where customers bought few products together

Irwan — Wed, 14 Aug 2024 06:13:38 GMT

han_rj

Then i guess the problem is in exporting option, not the DAX. If the DAX doesnt work, then it will show wrong result or it will show no result.

You might want to recheck your selection when exporting.

Thank you.

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 06:19:03 GMT

if I exclude the filter I am able to view exported data[CSV file data], when the filter is on the exported data is blank

Re: Find Customer sales where customers bought few products together

Irwan — Wed, 14 Aug 2024 06:24:19 GMT

Hmm not sure why. I used this method multiple times and no issue when exporting.

Well if this not works, the next way is Jihwan_Kim's solution.

Thank you.

Re: Find Customer sales where customers bought few products together

Jihwan_Kim — Wed, 14 Aug 2024 07:23:41 GMT

Hi, I am not sure if it works in the other semantic model than the sample that I provided, but please try something like below.

Please check the attached pbix file.

sales total: = VAR _sales = SUM ( fct_sales[sales] ) VAR _productcountall = COUNTROWS ( FILTER ( VALUES ( dim_product[product] ), dim_product[product] IN { "chair", "sofa", "table" } ) ) VAR _productcountonlysales = COUNTROWS ( FILTER ( SUMMARIZE ( fct_sales, dim_product[product] ), dim_product[product] IN { "chair", "sofa", "table" } ) ) RETURN SWITCH ( TRUE (), HASONEVALUE ( dim_customer[customer] ), IF ( _productcountonlysales = _productcountall, _sales ), _sales )

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 09:06:16 GMT

Yea, Does not work for my model

Re: Find Customer sales where customers bought few products together

ThxAlot — Wed, 14 Aug 2024 10:15:10 GMT

For fun only, to showcase the powerful Excel formulas,

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 10:49:01 GMT

This is awesome, One small question is it possible to eliminate other rows and only have customer a,f in the matrix

Re: Find Customer sales where customers bought few products together

ThxAlot — Wed, 14 Aug 2024 14:12:00 GMT

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 15:06:09 GMT

Hi @ThxAlot , Please can you help me explain the flow of the above logic, It seems I will need to make few tweaks to mine, So understanding it will help me make the tweeks

Re: Find Customer sales where customers bought few products together

han_rj — Wed, 14 Aug 2024 16:25:11 GMT

Hi ThxAlot , Thank You for helping , As a last tweak at times I have entries of blank/nulls in my fact table along side customer,product sku , Where a solution to show only entries without blanks will be helpful