Progress Indicator Tables

[MauroBote]

Hi friends.

I'm a newbie on dax, and I need to obtain a summary progress indicator table with on power bi.

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

And I have to obtain 2 tables with the summary of the progress to the date:

• First table (full) considering all period
• Second table (2019) just considering current year planned activities and taking the data from 0 to 100%

Please if you can help me to obtain the measure formulas that I require to build those tables, it would be awesome.

[MFelix]

Hi @MauroBote ,

For the first table create the following two measures:

Real Percent = 
IF (
    HASONEVALUE ( Proyetos[Proyeto] );
    SUM ( Proyetos[Real] );
    AVERAGEX (
        SUMMARIZE (
            ALL ( Proyetos[Proyeto] );
            Proyetos[Proyeto];
            "Real_percent"; SUM ( Proyetos[Real] )
        );
        [Real_percent]
    )
)

Plan Percent = 
IF (
    HASONEVALUE ( Proyetos[Proyeto] );
    SUM ( Proyetos[Plan] );
    AVERAGEX (
        SUMMARIZE (
            ALL ( Proyetos[Proyeto] );
            Proyetos[Proyeto];
            "Plan_percent"; SUM ( Proyetos[Plan] )
        );
        [Plan_percent]
    )
)

Then just add it to a table final result below (also check PBIX file attach):

Regarding the second part can you please just explain a little better what you mean by: 

• Second table (2019) just considering current year planned activities and taking the data from 0 to 100%

How are you considering the calculation? I can get the values for the project A but for project B values are not matching.

Regards,

MFelix

[MauroBote]

Thanks a lot MFelix! Really you are super!

For table's second part, the key is consider just the activities of a period (by relative date filtering on visual).

Per example (same one): If the period filtered is the current year the project A has 3 activities: 1, 2 and 3, so the maximum percent that I could reach this current year is 75%. Then I must consider the total % of activities, 75%, as 100% and every activity change their % value to 33,33% = 25/75%.

I try to did it changing the following in the formula:

Real Percent = 
IF (
    HASONEVALUE ( Proyetos[Proyeto] );
    SUM ( Proyetos[Real] ) / SUM ( Proyetos[%] );
    AVERAGEX (
        SUMMARIZE (
            ALL ( Proyetos[Proyeto] );
            Proyetos[Proyeto];
            "Real_percent"; SUM ( Proyetos[Real] ) / SUM ( Proyetos[%] )
        );
        [Real_percent]
    )
)

but if I add to the table another variable as Date or Activity, the percent is shown is 100% for those new lines from variables.

Please your help!