Forum Discussion

okusai3000's avatar
okusai3000
Icon for Helper IV rankHelper IV
5 years ago
Solved

SUMMARIZE not Working properly

Hello,

 

First of all, I'd like to introduce what I'm trying to get. I have the following table, where Antiguedad means how many days has that device been without changing their status. In other words, how long has been "AVAILABLE".

 

 

To get that Antiguedad I create a Measure that works fine:

 

*Antiguedad:

= DATEDIFF(
(MAXX(ALLSELECTED('dim Fecha'),Min('dim Fecha'[Fecha]))),[*Fecha Ultimo movimiento],DAY)

 

It's the difference between the month selected, and the date of the last status change.

*Fecha Ultimo movimiento:=

 

VAR fecha_ultimo_mov =

 

CALCULATE(

LASTDATE('fact Logistica_StockEquipos'[FECHA]),filter(all('dim Fecha'[Fecha]),'dim Fecha'[Fecha]<MIN(('dim Fecha'[Fecha]))),filter(all('dim Logistica_TipoEstado'[descrip_estado]),'dim Logistica_TipoEstado'[descrip_estado]<>SELECTEDVALUE('dim Logistica_TipoEstado'[descrip_estado])))

 

RETURN
if(ISBLANK(fecha_ultimo_mov),CALCULATE(FIRSTDATE('fact Logistica_StockEquipos'[FECHA]),all('dim Fecha')),fecha_ultimo_mov)

 

 

So far, so good, but I would like to get the average of days from that table. In other words, it's an average based on a measure and in order to get it right, I was told to use the function SUMMARIZE.

 

So, it goes like this:

 

*Antiguedad AVG =
AVERAGEX(
              SUMMARIZE('fact Logistica_StockEquipos','fact Logistica_StockEquipos'[Esap_cod],"antiguedad",[*Antiguedad]),                                   [antiguedad])
 
The problem is that this retrieves me a "0" and that's not the right value. I believe that the issue it's inside of the SUMMARIZE because I also tried with SUMX and neither worked.
 
Any idea what's going on?
 
thanks a lot!
 
Need Help
  • DataZoe's avatar
    DataZoe
    5 years ago

    okusai3000 It is a bit hard to troubleshoot without the pbix file because the measures are a bit complex. My first try would be to see if this gives you the value you are looking for:

     

    *Antiguedad AVG =
    AVERAGEX ( DISTINCT ( 'fact Logistica_StockEquipos'[Esap_cod] ), [antiguedad] )

4 Replies

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    okusai3000 It is a bit hard to troubleshoot without the pbix file because the measures are a bit complex. My first try would be to see if this gives you the value you are looking for:

     

    *Antiguedad AVG =
    AVERAGEX ( DISTINCT ( 'fact Logistica_StockEquipos'[Esap_cod] ), [antiguedad] )