Power Bi DAX: Measure that divides sale amount by 5% under a condition

[paulfink]

Hi guys,

Been tasked with making a calculation that would go something like this:

If 'cumulative' equals to or greater than target then divide 'Sales' by 0.5

Also, I need another measure that if the Cumulative is over the target then it should give 5% (See Dummy Date based on Actual Data)

It is for Commission so if a Salesperson goes over their target they receive a 5% commission

I tried making a measure for this but it wouldn't let me put in the Target, would only allow other measures.

This is what my data looks like.

For example, if Antony got 1200 then he would receive a 5% bonus of that 1200 so it would get 1260 in total. The target is from its own table, it looks like the table above.

The black is my current table (dummy data) and the red is what I want the measures to show.

The Commission Amount should be 5% of the cumulatives that are higher than the target. So Antony's target is 1000, the 5% starts at the 1040.

I need 2 measures now. One to show if the Cumulative is greater than or equal to the target then to show 5% on the table and another measure to show the 5% of the cumulative that is equal to or greater than the target.

My Targets are in a different table, everything in my Dummy Data is from one table I've imported from an SQL Server. All it shows is Sales Person - Date - Target

Does this make more sense?

[v-gizhi-msft]

Hi,

Please add an index column first in Query Editor.

Then try these two measures:

Commission = 
IF (
    MAX ( 'Table 1'[Cumulative] )
        >= CALCULATE (
            MAX ( 'Table 2'[Target] ),
            FILTER (
                'Table 2',
                'Table 2'[Salesperson] IN FILTERS ( 'Table 1'[Sales Person] )
            )
        ),
    0.05,
    0
)

Comm Amount = 
SUMX (
    DISTINCT ( 'Table 1'[Index] ),
    CALCULATE ( [Commission] * MAX ( 'Table 1'[Cumulative] ) )
)

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[Anonymous]

Hello

Try the following measurements.

Commissioning (above)

var target to MAX('Target_Table'[Objective])

var Cumm á MAX(Dummydata[Cummulative])

Was performance result1 at IF (Cucumber > Target, "5%", "0%")

return Result1

Amount of communication (Comm Amount)

var target to MAX('Target_Table'[Objective])

var Cumm á MAX(Dummydata[Cummulative])

var Result1 á IF(Cumm>target, DIVIDE(Cumm*0.5,10),0)

return Result1

Did I answer your question? If so, give me a Kudo

[Anonymous]

Hi,

Try the below measures.

Commision =

var target = MAX('Target_Table'[Target])

var Cumm = MAX(Dummydata[Cummulative])

var Result1 = IF(Cumm>target,"5%","0%")

return Result1

Comm Amount =

var target = MAX('Target_Table'[Target])

var Cumm = MAX(Dummydata[Cummulative])

var Result1 = IF(Cumm>target, DIVIDE(Cumm*0.5,10),0)

return Result1

Did I answer your question? If Yes, Give me a Kudo

[paulfink]

Thanks for the reply, but i got an error:

"The MAX function only accepts a column reference as the argument number 1."

The Cumulative Sum is its own measure so i had to remove the MAX from the Cumulative sum and it works perfectly know.

Is there a way to exclude a certain sales person? 

Also, the Comm Amount is not totalling all the values. Could you change your formula so it shows the total? It is only showing the final amount in the table.

[v-gizhi-msft]

Hi,

Please check my attached pbix file again.

If you still have any issue, could you please share some sample data as screenshots?

Best Regards,

Giotto

[paulfink]

My cumulative is a measure too:

CALCULATE(SUM('Table3'[SalesValue]), FILTER(ALLSELECTED'Table3'), 'Table3'[Index] <= MAX(''Table3''[Index])))

So when i put MAX it comes back with an error, The MAX function only accepts a column reference as the argument number 1.