Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CoreyL
Frequent Visitor

Expression to add children sales to revenue pool

 

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 !

 

 

2 REPLIES 2
v-alq-msft
Community Support
Community Support

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.