topic Re: Unify two formulas in DAX Commands and Tips

Unify two formulas

pfinanciero — Wed, 10 Nov 2021 16:18:58 GMT

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!

Re: Unify two formulas

smpa01 — Tue, 09 Nov 2021 15:32:16 GMT

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

Re: Unify two formulas

v-yanjiang-msft — Wed, 10 Nov 2021 05:36:29 GMT

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" ),

)

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.

Re: Unify two formulas

pfinanciero — Wed, 10 Nov 2021 12:35:26 GMT

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

Re: Unify two formulas

pfinanciero — Wed, 10 Nov 2021 16:16:24 GMT

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