Find count of distinct brands shopped & most preferred brands

[Anonymous]

Hi All, 

I am new to Power BI and needed your help with couple of problems that I am facing.

I have got 3 tables as follows:

1. Sales table which has customer email_id, SKU & revenue

2. Product table which has SKU and brand

3. Customer table which has distinct email ids for all the customers

Sample sales & product tables are as follows 

SALES TABLEPRODUCT TABLE

Now, I want to add following calculations in the customer table:

1. Total number of distinct brands shopped by the customer. I am using the following calculated column for this

brand_count = CALCULATE(DISTINCTCOUNT(product_table[brand]),sales_table, filter(sales_table, sales_table[customer] = customer_table[customer] ))

Although it is working fine, but it takes ~20 minutes to run as my datasets are huge (<300,000 rows). 

Is there any way to speed it up? 

2. I also want to add 3 more columns to the customer table with 1st_brand_preference, 2nd_brand_preference & 3rd_brand_preference for that customer. Brand where a customer had the highest revenue is 1st_brand_preference and so on.

The final customer table should look something like this:

Customer table expected

I haven't been able to write this one out at all. 

Thanks in advance!

[Zubair_Muhammad]

@Anonymous 

For the preferences, try these

Ist preference =
CONCATENATEX (
    TOPN (
        1,
        CALCULATETABLE (
            VALUES ( product_table[Brand] ),
            TREATAS ( CALCULATETABLE ( VALUES ( sales_table[SKU] ) ), product_table[SKU] )
        ),
        CALCULATE ( SUM ( sales_table[Revenue] ) ), DESC
    ),
    [Brand],
    ","
)

2nd preference =
CONCATENATEX (
    TOPN (
        1,
        TOPN (
            2,
            CALCULATETABLE (
                VALUES ( product_table[Brand] ),
                TREATAS ( CALCULATETABLE ( VALUES ( sales_table[SKU] ) ), product_table[SKU] )
            ),
            CALCULATE ( SUM ( sales_table[Revenue] ) ), DESC
        ),
        CALCULATE ( SUM ( sales_table[Revenue] ) ), ASC
    ),
    [Brand],
    ","
)

[Zubair_Muhammad]

@Anonymous 

Please try this one

Brand Count =
CALCULATE (
    DISTINCTCOUNT ( product_table[Brand] ),
    CROSSFILTER ( sales_table[SKU], product_table[SKU], BOTH )
)

[Anonymous]

Hi @Zubair_Muhammad , 

Tried using these queries but they are giving me output for the entire dataset instead customer level output. As in, the 1st preference & distinct SKUs are being calculated for the entire sales data and not for each customer separately.

Thanks.

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Could you show me how tables are related?

Please see the attached file as well. I used your sample data and seem to get the desired results

[Anonymous]

Hi @Zubair_Muhammad , 

Thanks for the help. I fixed the tables' relationships and now the formulas work fine. 

The distinct count calculation still takes ~20 minutes though, so for that I modified the top preference calculation as follows:

COUNTROWS (
    TOPN (
        100,
        CALCULATETABLE (
            VALUES ( product_table[Brand] ),
            TREATAS ( CALCULATETABLE ( VALUES ( sales_table[SKU] ) ), product_table[SKU] )
        ),
        CALCULATE ( SUM ( sales_table[Revenue] ) ), DESC
    )
)

I put a fairly large n-value for TOPN to get the desired distinct count for the time being.

Is there any other better and quicker way to achieve this? 

Thanks again!

[Zubair_Muhammad]

@Anonymous 

Try using MEASURES instead of calculated columns.

If you could share your file, i will take a look at the options