Forum Discussion

afrutos's avatar
afrutos
Icon for Resolver I rankResolver I
4 years ago
Solved

APPLY SUM TO MY TOTAL

Hi there,
I have this matrix, where, I have in rows:
Date of delivery, and underneath, Provenance.
For Work amount, it is using the average of the work amount, as I can have more than one row for provenance, as shown in the tooltip. And this is doing fine for me.

But in the total at the 'Date Cast' level, I would need it to do the sum of the averages, instead of giving me the average as well.

I have tried creating a measure:

So for  'Fecha Reparto: 12-2017' it should be 685,82  

 

AV IMPORTEOBRASGAE=
SWITCH(TRUE(),
ISINSCOPE(Liquidaciones[PROCEDENCIA]), AVERAGE(Liquidaciones[ImporteObraSGAE]),
ISINSCOPE(Liquidaciones[F.Reparto]), SUM(Liquidaciones[ImporteObraSGAE]),
ISINSCOPE(Liquidaciones[P.Origen]), SUM(Liquidaciones[ImporteObraSGAE]))

 

Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    4 years ago

    afrutos , You have to try a measure like

     

    Sumx(Summarize(Liquidaciones , Liquidaciones[PROCEDENCIA], Liquidaciones[F.Reparto],Liquidaciones[P.Origen] , "_1",

    SWITCH(TRUE(),
    ISINSCOPE(Liquidaciones[PROCEDENCIA]), AVERAGE(Liquidaciones[ImporteObraSGAE]),
    ISINSCOPE(Liquidaciones[F.Reparto]), SUM(Liquidaciones[ImporteObraSGAE]),
    ISINSCOPE(Liquidaciones[P.Origen]), SUM(Liquidaciones[ImporteObraSGAE]))

    ), [_1])

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    afrutos , You have to try a measure like

     

    Sumx(Summarize(Liquidaciones , Liquidaciones[PROCEDENCIA], Liquidaciones[F.Reparto],Liquidaciones[P.Origen] , "_1",

    SWITCH(TRUE(),
    ISINSCOPE(Liquidaciones[PROCEDENCIA]), AVERAGE(Liquidaciones[ImporteObraSGAE]),
    ISINSCOPE(Liquidaciones[F.Reparto]), SUM(Liquidaciones[ImporteObraSGAE]),
    ISINSCOPE(Liquidaciones[P.Origen]), SUM(Liquidaciones[ImporteObraSGAE]))

    ), [_1])

    • afrutos's avatar
      afrutos
      Icon for Resolver I rankResolver I
      4 years ago

      I'll try, but why are you using "_1"?

    • afrutos's avatar
      afrutos
      Icon for Resolver I rankResolver I
      4 years ago

      It works perfectly, 
      But coult you please explain what did you do before the SWITCH? amitchandak 
      I'll appreciate

    • afrutos's avatar
      afrutos
      Icon for Resolver I rankResolver I
      4 years ago

      Hi again,
      Now for example in this case, wich is different I have a matrix with:
      in rows : 

      'CP  2'[PERIODO ORIGEN]
      'CP 2'[FECHA REPARTO]

      In values I have:
      'CP 2'[porcentajetotalautorobra] and [TOTALIMPORTEAUTOR]
      So what I need isto clculate the [IMPORTE OBRA], and it should be 
      (100*[TOTALIMPORTEAUTOR]) / [porcentajetotalautorobra])
      So for not to get Infinit I made: 
      [CP OBRA ] =SWITCH(TRUE(),
      sum('CP 2'[porcentajetotalautorobra])=0, 0,
      SUMX('CP 2',((100*[TOTALIMPORTEAUTOR])/[porcentajetotalautorobra])))
       
      On the other hand, I should calculate €/min, and it should be: 
      [CP OBRA ]/SUM('CP 2'[Duracion]
      So for not to get Infinit  again, I made: 
      €/MIN  =
      SWITCH(TRUE(),
      SUM('CP 2'[Duracion])=0,0,
      [CP OBRA ]/SUM('CP 2'[Duracion]))

      But now I would like to calculate the correct sum in the matrix for my total, instead of having Infinit

      Could you help me with this other example? amitchandak 
      thanks in adcanced!