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
scotchnhand
Frequent Visitor

DAX Sales Reporting with Two Salespeople

Hello all! We have an issue in our PoweerBI environment that I could use some help with. 

 

We have Accounts setup that Salespeople split the sales for sometimes up to a four person split. I need help splitting those sales for these split accounts into there respective Salesperson. 

 

Below is how the Database is currently laid out. We have a transaction level link in factsales utilizing the Group Key to the DimDales Group. The dimsalesgroup contains unique individual salepserson names (no Splits), the next link in using the GRPkey and bridgesalesgroup contain the Split percentage from there we have a link to the Split account name. 

 

This is the overall layout:

Sales_GRP.PNGdimsalesgroup.PNGbridgesalesgroup.PNG

 

 

 

 

dimsalesperson.PNG 

 

My question is at a transaction level how do I assign the Split perctage to of Sale to SLS 1 and then SLS 2? 

5 REPLIES 5
TomMartens
Super User
Super User

Hey,

 

based on  your PBIX I created this measure, I'm not sure if TOT_SALES is the measure of your interest, but basically you can adjust this measure to your needs:

Total_Sales Split = 
SUMX(VALUES('factSales'[groupKey])
    ,SUMX(
        'bridgeSalesGroup'
        ,[TOTAL_SALES] * CALCULATE(MAX('bridgeSalesGroup'[Split %]))
    )
)

This allows to visualize a simple table:

image.png

 Please be aware that due to the content of the bridge table, currently the measure results into a larger splitted value.

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

Hey,

 

this is a really interesting challenge.

But without some sample data, this will be unfortunately to cumbersome.

 

Please consider to create some sample data, upload a PBIX file to onedrive or dropbox and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Any help would be greatly appreciated. 

Hey,

 

currently I'm surfing through your data and I'm wondering about the split that is configured for the grpkey = 88.

 

I see this split:

image.png

 

Is this correct?

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

It is not. It appears there us a data entry error. SLS Key 67 should not be included in grp key 88. 

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.