Calculate Fees of salesman by allocating total sales

[Anonymous]

Hello there,

I am newbie to power bi and I got stacked with something, so I want your help.Please be kind.

I have two tables Sales and SalesManFees which looks like:
Table Sales:

Year	Month	Sales
2022	1	10
2022	2	30
2022	3	20
2022	4	40
2022	5	60

Table SalesManFees:

SalesMan ID	Year	Month	Percentage
1	2022	1	10
2	2022	1	40
3	2022	1	50
1	2022	2	10

What I  want to archive is to take the sum of sales for every month/year  and multipliy it for every salesman with the given percentage.

So the desired output will be like :

SalesMan ID	Year	Month	Fees
1	2022	1	10 * 0,10=1
2	2022	1	10 *0,4 = 4
3	2022	1	10 *0,5= 5
4	2022	2	30 * 0*10=1

How can I archive this?

Thanks in advance!

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is a sample file with the solution https://we.tl/t-yfxeDFXk7Z

Fees = 
SUMX ( 
    SalesManFees,
    SUMX (
        Sales,
        VAR CurrentYear = SalesManFees[Year] 
        VAR CurrentMonth = SalesManFees[Month]
        VAR CurrentPercentage = SalesManFees[Percentage]
        VAR SalesYear = Sales[Year]
        VAR SalesMonth = Sales[Month]
        VAR SalesAmount = Sales[Sales]
        RETURN
            IF (
                CurrentYear = SalesYear && CurrentMonth = SalesMonth,
                SalesAmount * CurrentPercentage / 100
            )
    )
)

[daXtreme]

Here's a link to the solution. The numbers are slightly different since I've changed the input data a bit. But the calculations are correct.