Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Combining Totals from 2 different tables

Hi everyone,   I need a help regarding combining the results of 2 different source tables in Power BI. Please see below:   The first table is static, with manual input, where the dates defi...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi,

Sorry but it seems that I cant attach an excel or zip file on here so, I can only give a screenshot of the sample data. Please see below images.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ryan_mayu ,

     

     

    I tried to do your sample, however I have a zero result.

    Actually, I am newbie in PowerBI and started using the application 3 days ago.

    All other reports were already done exept of the final result which is computation of Actual/Target.

    So here are the steps I did:
    1. Create a department table 

    Department = DISTINCT('Target Name'[Department])
     
    2. Create a Date Table
    Selection Date = CALENDAR(MIN('Target'[R72 Date]),MAX('Target'[R72 Date]))
     
    3. On the actual value table, I created the calculation column:
    Performance = sum('Actual Data'[Total Processed)/SUM('Target'[TargetValue])
     
    4. From the relationship connection, I linked "R72 Date" and "Actual Date" to step 2 date, and 'Target'[Department] and 'Actual Data'[Department] to step 1 department.

    5. Finally From a matrix table:
    Rows = (Created Department)
    Column = (Created Date)
    Values = (Performance Calculation)
     
    Is there any steps I missed?
     
    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      3 years ago

      do you create relationships between tables?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi ryan_mayu ,

         

        Yes I did. (Step 4)
        I am thinking, I think I am able to create the calculation I need.

        Though it is a different approach, your sample gave me an idea to do it.

        Here is my thought:
        1. Clean the 2 soruce table, remove un needed columns from actual and target

        2. Make the names of common columns identical for both table

        3. Append the 2 tables. This will just add another column which is not present on the other table.

        4. Create a computation matrix on the fields dividing the 2 concerned columns

        5. Arrange the columns in the matrix as usual.

         

        Feel free to comment if you have a better suggestion or the procedure I did have a consequence.

        Thank you so much for your help and time!
        Your sample help me so much!