cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## DAX Expression - Sellers goals (sell and collection)

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.

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

1 ACCEPTED SOLUTION
Community Support

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

``````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.

4 REPLIES 4
Frequent Visitor

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.-

Community Support

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

``````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.

Frequent Visitor

Hi! Zhengdong Xu,

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

Thanks again.

Best regards,

Guillermo.-

Community Support

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors