Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate with pivot tables

Hello Everyone!

 

I want to do something in PBI wich is simple in excel, I have a database with countries and troops sent from the orign to a destination, the idea is that I want to know how many troops each country sent and how many retun in the same direction, 

For example Germany sent to India 15, and India returned 12, so the result will be 2 troops which are pending to retun to germany

I created two regular tables, but I dont know how to do the result table which is the difference between the two regular pivot tables above

 

Database:

This is the data form one week so I want to see the result each week

Pivot Tables

 

Result table

 I dont mind if i have to calculte new summarized tables as long I can get the last table

Thanks for the help!

  • Anonymous,

     

    You may add a calculated column first.

    Column =
    LOOKUPVALUE (
        Table1[Troops],
        Table1[Destination], Table1[Origin],
        Table1[Origin], Table1[Destination]
    )
    

1 Reply

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support

    Anonymous,

     

    You may add a calculated column first.

    Column =
    LOOKUPVALUE (
        Table1[Troops],
        Table1[Destination], Table1[Origin],
        Table1[Origin], Table1[Destination]
    )