Find Customer sales where customers bought few products together

[han_rj]

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.

[ThxAlot]

For fun only, to showcase the powerful Excel formulas,

[han_rj]

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

[ThxAlot · Accepted solution]

[han_rj]

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

[han_rj]

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

[Jihwan_Kim]

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
    )

[han_rj]

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

[Jihwan_Kim]

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.

[han_rj]

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

[Jihwan_Kim]

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
    )

[han_rj]

Yea, Does not work for my model

[Irwan]

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.

[han_rj]

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

[Irwan]

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.

[han_rj]

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

Power BI desktop Data :

Excel Data 

[Irwan]

@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.

[han_rj]

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

[Irwan]

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.