Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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:
I haven't been able to write this one out at all.
Thanks in advance!
Solved! Go to 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
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], "," )
@Anonymous
Please try this one
Brand Count = CALCULATE ( DISTINCTCOUNT ( product_table[Brand] ), CROSSFILTER ( sales_table[SKU], product_table[SKU], BOTH ) )
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.
@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
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!
@Anonymous
Try using MEASURES instead of calculated columns.
If you could share your file, i will take a look at the options
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |