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 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?

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    you can paste your raw data as table here. that will be better than screenshot

    something like below

    a b c
    1 3 5
    2 4 6
  • 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Yes, I will do that next time.

    Thanks!

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    you are welcome