Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |