Forum Discussion

MauroBote's avatar
MauroBote
Helper 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 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.

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 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
      Helper I
      6 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!