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.
There are two SKU's. Customer(A) who purchases a SKU gets a 'share' of a revenue pool. If Customer(A) referrs another user who in turn buys one of the two SKU's, customer(A) is eligible for another share.
Presently I have "customer(A) (User Number) who purchases a SKU gets a 'share' of a revenue pool." completed. It's the logic for the second part which I am struggling to sort out.
Some context re: the data is follows:
KoreaFounder - table w/ column displaying the users who purchased a SKU, their 'founderStar' rank (1 or 2) and 'shareValue' which is based on a percentage of total sales. ('founderStar' & 'ShareValue' are generated columns btw).
MeasuresTable - measures used in other tables, where I calculate the percentage of revenue avail, and share value each user receives (which is a percentage of the revenue pool)
Sales - table lists all sales (which include SKU )
UniquePaidUsers - table lists all paid invoices and includes logic to allocate a 'share' to the user, see below:
APStarFounder = Switch(TRUE(),
CountRows(filter(Sales,Sales[Item Code]="9002kr" && Sales[User Number]=UniquePaidUsers[User#]))>0,2,
CountRows(filter(Sales,Sales[Item Code]="9001kr" && Sales[User Number]=UniquePaidUsers[User#]))>0,1)
Important to note: the 'Sales' table includes an 'enroller' column for each sales item (row). So I know who the 'referrer' is for all sales.
I'm thinking the correct approach is to search the 'Sales' table for all sales which include one of the SKU's (Sales[Item Code]="9001kr" or Sales[Item Code]="9002kr" ) AND filter by Sales[Enroller]
but what would that expression look like ? See attached for working example.
https://www.dropbox.com/s/k3ts0hrxk35ix3r/revenue-pool-calculations.zip?dl=0
Thank you for any advice, and stay safe out there !
Hi, @CoreyL
I wonder what your expected result is.
'I'm thinking the correct approach is to search the 'Sales' table for all sales which include one of the SKU's (Sales[Item Code]="9001kr" or Sales[Item Code]="9002kr" ) AND filter by Sales[Enroller]'.
You may try like below.
APStarFounder =
Switch(
TRUE(),
CountRows(
filter(
Sales,
Sales[Item Code] in {"9001kr","9002kr"}
&& Sales[User Number]=UniquePaidUsers[User#]
)
)>0,2,1
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I solved a similar referral problem using transitive closure, let me see if I can find the thread. If I can't, see Transitive Closure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/m-p/783828#M388
Oh, found it: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Hierarchy-Sales-for-Entire-Path/td-p/904188
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |