Forum Discussion

pfinanciero's avatar
pfinanciero
Frequent Visitor
4 years ago

Unify two formulas

 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!
DAX
Formula Help

4 Replies

    • pfinanciero's avatar
      pfinanciero
      Frequent Visitor
      4 years ago

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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's avatar
      pfinanciero
      Frequent Visitor
      4 years ago

      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