Commission Report

[zenisekd]

Hi, I am trying to build a commission report and I am trying to figure out, how to approach this with DAX. Could somebody help please? Here is my sample file: https://drive.google.com/file/d/1dwe8FduE6WPKyN7_oerXBxSLiCJFc6zK/view?usp=drive_link

Commissions are defined by the total sales of all saleman. Crucial are product categories assigned to the roles, which are evaluated. So if a salesman is assigned to gropu A (Salesman A), they received commisions based on total sales of all saleman, from products in B and C category.
 

Which in the example is Car total sales is 41, thus commission of 4 + Bus total sales 0, thus commission 0. Thus total commission 4. 

My request aims to help me calculate measures, which I can use to create columns for visuals ( added in paint in pictures bellow). Basically, I need to add columns, which if the salesman is selected will based on his role show the real total sale and show, which commission is a salesman owed.  If no salesman is selected, it will show for each category, where is the business and commissioin standing in general. Also in the down left corner is a table, which will show commissions values for the categories...  Pictures bellow should demonstrate it.

Thank you for your help. 

[lbendlin]

Here's how I would restructure the data model

I would also include a Products table and make sure the Product key is consistent.

This will make it much easier to compute the commissions. Do you want to calculate them by sales role, by sales rep , or in some other way?

[Anonymous]

Hi @zenisekd ,

You have a many-to-many relationship between the two tables, and the values between them will affect the commission calculation, I just tried it and found that there will always be multiple values that cannot correspond to each other.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.