Unify two formulas

[pfinanciero]

 Hi, I need some help please.

I have two formulas and I need to join them in one.

1) The first one is:

Limit Rate =

VAR NetMargin =

DIVIDE(

SUM(Tabla1[PYG - Mes]),

CALCULATE(SUM(Tabla1[PYG - Mes]),Tabla1[Grupo]="Ingresos Netos"),0)

RETURN

CALCULATE(

SELECTEDVALUE(Tabla2[Rates],0),

Tabla2[Limite inferior] <= NetMargin,

Tabla2[Limite superior] > NetMargin)

2) The secod one is:

Medida =

IF(

HASONEVALUE(Tabla1[Mes Año]),

[Limit Rate],

AVERAGEX(VALUES(Tabla1[Mes Año]),[Limit Rate]))

Let me know if you have any question, thanks!

[v-yanjiang-msft]

Hi @pfinanciero ,

According to your description, here’s my solution.

Medida =

VAR _NetMargin =

    DIVIDE (

        SUM ( 'Tabla1'[PYG-Mes] ),

        CALCULATE ( SUM ( 'Tabla1'[PYG-Mes] ), 'Tabla1'[Grupo] = "Ingresos Netos" ),

        0

    )

VAR _LimitRate =

    CALCULATE (

        SELECTEDVALUE ( 'Tabla2'[Califacion], 0 ),

        'Tabla2'[Limite inferior] <= _NetMargin,

        'Tabla2'[Limite superior] > _NetMargin

    )

RETURN

    IF (

        HASONEVALUE ( 'Tabla1'[Mes Ano] ),

        _LimitRate,

        AVERAGEX ( VALUES ( 'Tabla1'[Mes Ano] ), _LimitRate )

)

Best Regards,
Community Support Team _ kalyj

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

[pfinanciero]

Hi,thanks for your reply.

I included it in the model as follows (the measure is called "Calificación"):

Califiación =

VAR NetMargin =

DIVIDE(

SUM(Tabla1[PYG - Mes]),

CALCULATE(SUM(Tabla1[PYG - Mes]),Tabla1[Grupo]="Ingresos Netos"),0)

VAR Ratio =

CALCULATE(

SELECTEDVALUE(Tabla2[Rates],0),

Tabla2[Limite inferior] <= NetMargin,

Tabla2[Limite superior] > NetMargin)

RETURN

IF(

HASONEVALUE(Tabla1[Mes Año]),

Ratio,

AVERAGEX(VALUES(Tabla1[Mes Año]),Ratio))

However, I can't find the solution, because in the total row I need to calculate the average of the grade as shown in the column "Medida", which is calculated based on the measure called "Limit Rate".
I hope to be clearer this time, thank you very much

[smpa01]

@pfinanciero  is it kindly possible to supply a sample pbix please?

[pfinanciero]

Excuse me I'll give a bit of context. The first measure is used to give a rating from 1 to 5 to the net margin according to some preset ranges in another table. Since in the row of totals of the first formula it does not calculate the average grade for me, I had to create the second measure, with which it calculates the average in the row of totals.

My question is these two formulas cannot be joined into one, I attach screenshot of the table with current situation.

Thanks!!