DAX Expression - Sellers goals (sell and collection)

[GuilleLus]

Hi! 

I needs to calculate the economic incentive for sellers who achieve their sales and collection (%) goals.

The incentive is paid only if both objectives are met (Incentive_1). If sales exceed the set goal by 20%, an additional amount is paid (Incentive_2)

I send attached document with the FACT and DIM tables.  I hope you can interpret the tables.

Thaks for your collaboration.

Best regards, Guillermo.-

Tabla_DIM_Agentes

ID_Agente	Nombre_Agente	ID_Grupo
54000	Almeyda Lorenzo	1
74000	Colucci Mariano	2
120000	Almada Martina	2
131000	Abel Zapatero	1
174000	Ceschi Juan Atilio	1
200000	Del Pino Hugo	3
221000	Bottinelli Diego	2
275000	Alejandra Cacheuta	2
415000	Carlos Ruppen	3

Tabla_DIM_Incentivos

ID_Grupo_Objetivos	Incentivo_1	Incentivo_2
1	1000	1500
2	1300	2000
3	2000	3000

 Tabla_DIM_Objetivo_Mensual

ID_Agente	Objetivo_Venta (Sales)	Objetivo_Cobranza
54000	25	60
74000	50	60
120000	70	60
131000	20	60
174000	15	60
200000	100	60
221000	65	60
275000	40	60
415000	120	60

 Tabla_Fact_Ventas

ID_Agente	Venta_Mensual	Cobranza_Mensual (%)
54000	18	70
74000	72	81
120000	71	58
131000	19	58
174000	16	84
200000	95	78
221000	78	59
275000	35	61
415000	132	61

[GuilleLus]

Zhengdong Xu, thanks for your help.

The interpretation is correct. The calculate column is OK, but some results are not as expected.

I Send image for to review it.

Results OK & Results NOT OK

Thaks again.

Best regards, Guillermo.-

[v-zhengdxu-msft · Accepted solution]

Hi @GuilleLus 

Maybe I'm misunderstanding what you mean, I thought more than 20% of the members were (Incentivo_1+ Incentivo_2)

please try this:

Incentivo = 
VAR _Sales_target =
    RELATED ( DIM_Objetivo_Mensual[Objetivo_Venta (Sales)] )
VAR _Cobranza_target =
    RELATED ( DIM_Objetivo_Mensual[Objetivo_Cobranza] )
VAR _Cobranza_percentage =
    DIVIDE ( 'Fact_Ventas'[Cobranza_Mensual (%)], _Cobranza_target )
VAR _Venta_percentage =
    DIVIDE ( 'Fact_Ventas'[Venta_Mensual], _Sales_target )
RETURN
    IF (
        _Cobranza_percentage >= 1
            && _Venta_percentage >= 1,
        IF (
            _Venta_percentage >= 1.2,
            RELATED ( DIM_Incentivos[Incentivo_2] ),
            RELATED ( DIM_Incentivos[Incentivo_1] )
        )
    )

Best Regards

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

[GuilleLus]

Hi! Zhengdong Xu,

The problem was successfully resolved. I will accept your help it as the solution

Thanks again.

Best regards,

Guillermo.-

[v-zhengdxu-msft]

Hi @GuilleLus 

Please try this:

Add a calculate column in the table_Fact_Ventas:

Incentivo = 
VAR _Sales_target =
    RELATED ( DIM_Objetivo_Mensual[Objetivo_Venta (Sales)] )
VAR _Cobranza_target =
    RELATED ( DIM_Objetivo_Mensual[Objetivo_Cobranza] )
VAR _Cobranza_percentage =
    DIVIDE ( 'Fact_Ventas'[Cobranza_Mensual (%)], _Cobranza_target )
VAR _Venta_percentage =
    DIVIDE ( 'Fact_Ventas'[Venta_Mensual], _Sales_target )
RETURN
    IF (
        _Cobranza_percentage >= 1
            && _Venta_percentage >= 1,
        IF (
            _Venta_percentage >= 1.2,
            RELATED ( DIM_Incentivos[Incentivo_1] )
                + RELATED ( DIM_Incentivos[Incentivo_2] ),
            RELATED ( DIM_Incentivos[Incentivo_1] )
        )
    )

The result is as follow:

The pbix is as below:

Best Regards

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