Forum Discussion

philpringuer's avatar
philpringuer
Frequent Visitor
7 years ago
Solved

Create a Calculated Column from Different Tables

Hi Team - apologies in advance; new to PBI... I'm working with, say, a table containing thousands of burns. Each burn has a PLANNED AREA, and a DISTRICT that it falls within.   I'm able to summ...
Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi philpringuer,

    According to your description, my understanding is that you want to create a new table which contains the summarize data and target data of each DISTRICT.

    In this scenario, we can create two calculated column in Table B like below:

    TOTAL PLANNED AREA = CALCULATE(SUM(TableA[PLANNED AREA]),FILTER(ALL(TableA),TableA[DISTRICT]= TableB[DISTRICT]))

Done Planning = DIVIDE(TableB[TOTAL PLANNED AREA],TableB[TARGET AREA])

    The result will like below:

    For the problem how to display the hierarchy (REGIONS -> DISTRICTS), we can drag the DISTRICTS to REGIONS to create a hierarchy. Then drag the hierarchy and TOTAL PLANNED AREA and other columns to Matrix. After that click the below button 2.

    The result will like below:

    Best Regards,

    Teige

philpringuer's avatar
philpringuer
Frequent Visitor
7 years ago

Hi Teige - thank-you v.much for your response - I was away from work Friday, and am trying out your answer now ... and will confirm once I've followed your suggestions.

Again, thanks for taking the time to help me.

 

phil

philpringuer's avatar
philpringuer
Frequent Visitor
7 years ago

... and, it's a WINNER!  

 

Thanks so much Teige - wherever you are, I owe you a coffee / beer - if you're visiting Melbourne, you can collect.  Now off to understand  it.


phil