Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

merging two calculated tables

Hello,   I have a source table " ProjectAuditsDB" with values similar to these. A Project will have more than 1 audit throughout time.   ProjectAuditID ProjectID AuditDate BIM Quality Scor...
Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    hi Anonymous

     

    Try with this:

     

    UnionTables = Union(SUMMARIZE('ProjectAuditsDB_LAST AUDITS SCORE';'ProjectAuditsDB_LAST AUDITS SCORE'[ProjectID];
    'ProjectAuditsDB_LAST AUDITS SCORE'[Project Status];"Status";"Audited");SUMMARIZE('ProjectAuditsDB_NOT

    AUDITED';'ProjectAuditsDB_NOT AUDITED'[ProjectID];'ProjectAuditsDB_NOT AUDITED'[ProjectStatus];"Status";"Not Audited")

  • Vvelarde's avatar
    Vvelarde
    10 years ago

    Anonymous

     

    To obtain these results, use this:

     

    ProjectAuditsDB_1ROW_BEFORE LAST AUDITS SCORE =
    SUMMARIZE (
        ProjectAuditsDB;
        ProjectAuditsDB[ProjectID];
        "AVG-LAST AUDIT"; CALCULATE (
            AVERAGE ( ProjectAuditsDB[BIM Quality Score] );
            FILTER (
                ProjectAuditsDB;
                ProjectAuditsDB[AuditDate]
                    = MINX (
                        TOPN ( 2; ProjectAuditsDB; ProjectAuditsDB[AuditDate] );
                        ProjectAuditsDB[AuditDate]
                    )
            )
        )
    )

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
10 years ago

hi Anonymous

 

Try with this:

 

UnionTables = Union(SUMMARIZE('ProjectAuditsDB_LAST AUDITS SCORE';'ProjectAuditsDB_LAST AUDITS SCORE'[ProjectID];
'ProjectAuditsDB_LAST AUDITS SCORE'[Project Status];"Status";"Audited");SUMMARIZE('ProjectAuditsDB_NOT

AUDITED';'ProjectAuditsDB_NOT AUDITED'[ProjectID];'ProjectAuditsDB_NOT AUDITED'[ProjectStatus];"Status";"Not Audited")

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Fenomenal!

    Muchisimas gracias Vvelarde.

     

    I know I am pushing my luck here but remember that other Summarize table for Last Audits Score?

     

    Is there also way to create a similar summarized table but with the audits before last?

     

    I was trying to create a KPI visual comparing the Last Audits Average Score with the Audits Before Last Audits.

     

     

    Hello from Manchester, UK, with a slight portuguese accent.

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      10 years ago

      In  new table Summarize

       

      And Only Change =Max(ProjectAuditsDB[AuditDate])

       

      To  <>Max(ProjectAuditsDB[AuditDate])

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Hi Vvelarde

         

        It is not reporting the info from the audits before last.

        See image below.

        At the top, form all audits list, you can see all of 433PPC project's info.

         

        I have copy pasted the summarized tables to excel, side by side.

        On the left side > Last Audits

        On the right side > Before Last Audits

         

        The numbers for the Before Last Audits are not correct when compared to the main audits list.