Expression to add children sales to revenue pool

[CoreyL]

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 !

[v-alq-msft]

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.

[Greg_Deckler]

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