cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Indicadores para avances de proyectos / Indicators for project progress

Hola amigos.

Soy un novato en esto de power bi y dax, pero necesito realizar un gran trabajo para un tablero y por más que le he dado vueltas no he podido llegar a destino.

Cuento con una tabla de una base de datos con una estructura similar a la siguiente:

Tabla BBDD.png

Y necesito realizar lo que tratare de explicar a continuación:

A) Necesito obtener una tabla resumen con el avance por proyectos a la fecha actual, tanto planificado como real, siguiendo el mismo ejemplo sería algo así:
Tabla x proyecto full.png

B) Y lo mismo pero solo considerando las actividades planificadas para el 2019:

Tabla x proyecto 2019.png

C) Luego tambien necesito obtener los promedios de avance para todo el periodo (lo cual tengo pensado visualizar en un medidor u odometro):

Indicadores de avance full.png

D) Y lo mismo pero solo para las actividades del 2019, considerando lo del 2019 de 0 a 100% (visualizando mediante un medidor u odometro):

Indicadores de avance 2019.png

E) Y por otro lado tambien necesito confeccionar la siguiente tabla con los avances acumulados por proyecto y como total promedio:

Tabla Avance Acumulado.png

F) Para terminar es necesario que genere las Curvas S de avance, ya sea como vista de todo el periodo (full):

CurvaS Full.png

G) Así como tambien considerando solo el 2019 (al 100%):

CurvaS 2019.png

H) Y tambien una vista acotada para el 2019, pero considerando los valores de todo el periodo (sin llevarlo a 100%):

CurvaS Full (2019).png

 

Favor si pueden ayudarme a orientarme con las formulas que requiero para cada cosa, sería sensacional.

 

Agradecido!

 

-----

Hi friends.

I'm a newbie on power bi and dax, and I need to do a great job for a board. I've tried to do it, but my efforts has been unsuccessful.

First I have a data base table with a structure similar to the following:

Tabla BBDD.png

And I need to do what I'm going to trie to explain next:

A) I need to obtain a summary table with the progress by projects to the current date, both planned and actual, following the same example would be something like this:

Tabla x proyecto full.png

B) And the same but only considering the activities planned for 2019:

Tabla x proyecto 2019.png

C) Then I also need to obtain the progress averages for the entire period (which I plan to visualize on a meter or odometer):

Indicadores de avance full.png

D) And the same but only for the activities planned for 2019, considering the 2019 from 0 to 100% (visualizing by means of a meter or odometer):

Indicadores de avance 2019.png

E) And on the other hand I also need to prepare the following table with the accumulated progress per project and as an total's average:

Tabla Avance Acumulado.png

F) To finish, I needo to generate the S Curses, viewing the entire period (full):

CurvaS Full.png

G) As well as considering only 2019 (100%):

CurvaS 2019.png

H) And also a bounded view for 2019, but considering the values ​​of the entire period (without bringing it to 100%):

CurvaS Full (2019).png

 

Please if you can help me to guide me with the formulas that I require for each thing, it would be awesome.

 

Grateful regards!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Indicadores para avances de proyectos / Indicators for project progress

Hi @MauroBote ,

 

Please check the following steps as below.

1. To create the calculated tables as below.

FULL = SUMMARIZECOLUMNS('Table'[PROYECTO],"AVP",SUM('Table'[PLAN]),"AVR",SUM('Table'[REAL]))
FullT = 
VAR a = { "total" }
RETURN
    ADDCOLUMNS (
        a,
        "AVP", SUM ( FULL[AVP] ) / DISTINCTCOUNT ( 'Table'[PROYECTO] ),
        "AVR", SUM ( FULL[AVR] ) / DISTINCTCOUNT ( 'Table'[PROYECTO] )
    )
2019 = 
SUMMARIZECOLUMNS (
    'Table'[PROYECTO],
    "AVP", DIVIDE (
        CALCULATE (
            SUM ( 'Table'[PLAN] ),
            FILTER ( 'Table', YEAR ( 'Table'[FECHA PLAN] ) = 2019 )
        ),
        CALCULATE (
            SUM ( 'Table'[%(1)] ),
            FILTER ( 'Table', YEAR ( 'Table'[FECHA PLAN] ) = 2019 )
        )
    ),
    "AVR", DIVIDE (
        CALCULATE (
            SUM ( 'Table'[REAL] ),
            FILTER ( 'Table', YEAR ( 'Table'[FECHA REAL] ) = 2019 )
        ),
        CALCULATE (
            SUM ( 'Table'[%(1)] ),
            FILTER ( 'Table', YEAR ( 'Table'[FECHA PLAN] ) = 2019 )
        )
    )
)
2019T = 
VAR a = { "2019" }
RETURN
    ADDCOLUMNS (
        a,
        "AVP", SUM ( '2019'[AVP] ) / DISTINCTCOUNT ( 'Table'[PROYECTO] ),
        "AVR", SUM ( '2019'[AVR]) / DISTINCTCOUNT ( 'Table'[PROYECTO] )
    )

2. To get a CALENDAR table by creating two more calculated tables.

Table 2 = CALENDAR(DATE(2018,9,30),DATE(2020,02,29))
CALENDAR = FILTER('Table 2',[Date] = CALCULATE(MAX('Table 2'[Date]),ALLEXCEPT('Table 2','Table 2'[Date].[Jaar],'Table 2'[Date].[MonthNo])))

3. After that, measures can help us to get the excepted result.

AVPA = CALCULATE(SUM('Table'[%(1)]),FILTER('Table','Table'[FECHA PLAN]<=MAX('CALENDAR'[Date]) && 'Table'[PROYECTO]="A"))
AVPB = CALCULATE(SUM('Table'[%(1)]),FILTER('Table','Table'[FECHA PLAN]<=MAX('CALENDAR'[Date]) && 'Table'[PROYECTO]="B"))
AVRA = var maxd = MAX('Table'[FECHA REAL])
return
IF(maxd<=MAX('CALENDAR'[Date]),BLANK(),
CALCULATE(SUM('Table'[REAL]),FILTER('Table','Table'[FECHA REAL]<=MAX('CALENDAR'[Date]) && 'Table'[PROYECTO]="A")))
AVRB = var maxd = MAX('Table'[FECHA REAL])
return
IF(maxd<=MAX('CALENDAR'[Date]),BLANK(),
CALCULATE(SUM('Table'[REAL]),FILTER('Table','Table'[FECHA REAL]<=MAX('CALENDAR'[Date]) && 'Table'[PROYECTO]="B")))

Capture.PNG

 

For the rest ones, I cannot get the logic of them, Could you please share more details about that to me?

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Indicadores para avances de proyectos / Indicators for project progress

Hi @MauroBote ,

 

Please check the following steps as below.

1. To create the calculated tables as below.

FULL = SUMMARIZECOLUMNS('Table'[PROYECTO],"AVP",SUM('Table'[PLAN]),"AVR",SUM('Table'[REAL]))
FullT = 
VAR a = { "total" }
RETURN
    ADDCOLUMNS (
        a,
        "AVP", SUM ( FULL[AVP] ) / DISTINCTCOUNT ( 'Table'[PROYECTO] ),
        "AVR", SUM ( FULL[AVR] ) / DISTINCTCOUNT ( 'Table'[PROYECTO] )
    )
2019 = 
SUMMARIZECOLUMNS (
    'Table'[PROYECTO],
    "AVP", DIVIDE (
        CALCULATE (
            SUM ( 'Table'[PLAN] ),
            FILTER ( 'Table', YEAR ( 'Table'[FECHA PLAN] ) = 2019 )
        ),
        CALCULATE (
            SUM ( 'Table'[%(1)] ),
            FILTER ( 'Table', YEAR ( 'Table'[FECHA PLAN] ) = 2019 )
        )
    ),
    "AVR", DIVIDE (
        CALCULATE (
            SUM ( 'Table'[REAL] ),
            FILTER ( 'Table', YEAR ( 'Table'[FECHA REAL] ) = 2019 )
        ),
        CALCULATE (
            SUM ( 'Table'[%(1)] ),
            FILTER ( 'Table', YEAR ( 'Table'[FECHA PLAN] ) = 2019 )
        )
    )
)
2019T = 
VAR a = { "2019" }
RETURN
    ADDCOLUMNS (
        a,
        "AVP", SUM ( '2019'[AVP] ) / DISTINCTCOUNT ( 'Table'[PROYECTO] ),
        "AVR", SUM ( '2019'[AVR]) / DISTINCTCOUNT ( 'Table'[PROYECTO] )
    )

2. To get a CALENDAR table by creating two more calculated tables.

Table 2 = CALENDAR(DATE(2018,9,30),DATE(2020,02,29))
CALENDAR = FILTER('Table 2',[Date] = CALCULATE(MAX('Table 2'[Date]),ALLEXCEPT('Table 2','Table 2'[Date].[Jaar],'Table 2'[Date].[MonthNo])))

3. After that, measures can help us to get the excepted result.

AVPA = CALCULATE(SUM('Table'[%(1)]),FILTER('Table','Table'[FECHA PLAN]<=MAX('CALENDAR'[Date]) && 'Table'[PROYECTO]="A"))
AVPB = CALCULATE(SUM('Table'[%(1)]),FILTER('Table','Table'[FECHA PLAN]<=MAX('CALENDAR'[Date]) && 'Table'[PROYECTO]="B"))
AVRA = var maxd = MAX('Table'[FECHA REAL])
return
IF(maxd<=MAX('CALENDAR'[Date]),BLANK(),
CALCULATE(SUM('Table'[REAL]),FILTER('Table','Table'[FECHA REAL]<=MAX('CALENDAR'[Date]) && 'Table'[PROYECTO]="A")))
AVRB = var maxd = MAX('Table'[FECHA REAL])
return
IF(maxd<=MAX('CALENDAR'[Date]),BLANK(),
CALCULATE(SUM('Table'[REAL]),FILTER('Table','Table'[FECHA REAL]<=MAX('CALENDAR'[Date]) && 'Table'[PROYECTO]="B")))

Capture.PNG

 

For the rest ones, I cannot get the logic of them, Could you please share more details about that to me?

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Indicadores para avances de proyectos / Indicators for project progress

Hi Frank.

 

Thanks for your responde. I try that you explained but I have one question: What's the trick to peak 2019 by visual filters?

 

I'll try to explain every table:

 

I think it might be useful if I start explaining the headings of the source table:

  • Proyecto = Projects
  • Actividad = Activities from projects
  • % = Percentage weight of activities on project
  • Fecha Plan = Planification date when the activity must be done
  • Fecha Real = Actual or real date when the activity was effectively done
  • CP (Cumplimiento Plan) = Accomplishment plan. It turns 100% when a "Fecha Plan" is earlier than today's date
  • CR (Cumplimiento Real) = Accomplishment real. It turns 100% when a "Fecha Real" exist
  • Plan = % * CP
  • Real = % * CR

 

A) Headers:

AVP (Avance Plan) = Planification progress for the entire period. Sum of every "Plan" per projects = SUM(Plan)

AVR (Avance Real) = Actual progress for the entire period. Sum of every "Real" per projects = SUM(Real)

 

B) Headers are the same explained previously but only considering the activities planned for the current year period (by use of relatives filter of the visual table), and considering them as the 100% of the projects

AVP = SUM(Plan / SUM(%))

AVR = SUM(Real / SUM(%))

First AVP = 25%/75% + 25%/75% + 25%/75% = (25% + 25% + 25%)/(75%)

First AVR = 25%/75% + 25%/75% + 0%/75% = (25% + 25% + 0%)/(75%)

 

C) Average of values obtained in table A)

First Total = AVERAGE(75%;30%)

Second Total = AVERAGE(50%;55%)

 

D) Same of C) but using the results of table B)

First Total = AVERAGE(100%;44%)

Second Total = AVERAGE(67%;100%)

 

E) The date column was completed with the end days of each month determined between the earliest - 1 month (between project A and B) and the latest date (between project A and B)

The table has tree diferent calculation:

First (Proyectos): Planification (AVP A, AVP B) and Actual (AVR A, AVR B) accumulated progress per month and per project. The actual progress is calculated until the last month over

Second (Full): Average accumulated progress of first calculation

Third (2019): Average accumulated progress considering just activities of the current year divided by total of % planificated for the current year as well, same thing of B). It is importante for these to obtain the current's year accumulated progreess per project if someone use visual filters 

 

F) S Curve for the second calculation of E)

 

G) S Curve for the third calculation of E)

 

H) Same of F) but considering the current year period (without dividing by sum of % per project) on the graph

 

Looking forward your response.

 

Thanks a lot in advance!

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors