Forum Discussion

MauroBote's avatar
MauroBote
Icon for Helper I rankHelper I
7 years ago

Progress Indicator Tables

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 ...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

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's avatar
MauroBote
Icon for Helper I rankHelper I
7 years ago

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!