Power BI How to use a measure to get Max

Question

I have a table in my visualization.

with the following fields

Level1, Type ID, Group ID, Starting Value, Commission, Sales Amount, Calc Bonus  Bonus 
1       3        1         €5000           1           €9,838         €98.38        
1       3        1         €7500           2           €9,838         €196.75     €196.75
1       3        1         €10000          3           €9,838         
1       3        1         €12500          4           €9,838 
1       3        1         €15000          5           €9,838 
1       3        1         €17500          6           €9,838 
1       3        1         €20000          7           €9,838 
1       3        2         €20000          1           €119,231       €1,192.31
1       3        2         €40000          2           €119,231       €2,384.63
1       3        2         €60000          3           €119,231       €3,576.94    €3,576.94

I calculated in a measure BONUS Calc with

BONUS Calc = 
     SUMX(FILTER('table', 
          AND ('table'[Type] = 3, 
               'table1'[Sales Amount] >=  'Table'[Starting Value])),
          'table1'[Sales Amount] * 'table'[Commission])

But what I want is the column Bonus

Basically, I want to filter by Level 1, Type ID, Group ID Check the first Starting value <= Sales Amount and calculate de Commission... The problem is I have a calculation for all the Starting value <= Sales Amount I only want the first one.

I thought I can do a Max with filters to my measure column Bonus Calc but I didn't find the solution...

I appreciate if someone can give me a clue. Thanks

Dim Group
Groupkey  Group 
1         group1
2         group2
3         group3
4         group4
5         group5

Table1 
Groupkey SalesAmount 
1        €9838
2        €119231
3        €

Table 
Level1 ID Level1 GroupKey Group   Type ID Type  Starting value Comission
1         Lev1   1        group1  1       type1 €5000          1%
1         Lev1   1        group1  1       type1 €7500          2%
1         Lev1   1        group1  1       type1 €10000         3%
1         Lev1   1        group1  1       type1 €12500         4%
1         Lev1   1        group1  1       type1 €15000         5%
1         Lev1   1        group1  1       type1 €17500         6%
1         Lev1   1        group1  1       type1 €20000         7%
1         Lev1   1        group1  2       type1 €20000         1%
1         Lev1   1        group1  2       type1 €40000         2%
1         Lev1   1        group1  2       type1 €60000         3%

Table and Table 1 aren't linked together they are fact tables and in between, there is a Dim table Group so the tables are linked to Dim Group — ofeliajesus
Provide some sample data and expected outcome for those 3 tables. — mxix

Joao Leal Joao Leal · Accepted Answer · 2020-11-12T10:20:33

So to calculate your commission you need to know the sales prices, and you'd do it like this:

comission = 
       var sales = SUM(table1[SalesAmount])
       RETURN CALCULATE(MAX(table[comission]), 
          ALL(table[starting value]), table[starting value] <= sales)

To calculate your bonus amount you just expand on the previous calculation:

bonus = 
    var sales = SUM(table1[SalesAmount])
    var comission = CALCULATE(MAX(table[comission]), 
      ALL(table[starting value]), table[starting value] <= sales)
    Return comission * sales

Power BI How to use a measure to get Max

1 Answers