cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
philpringuer Frequent Visitor
Frequent Visitor

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 summarise, by DISTRICT (row headings), the total PLANNED AREA - lets call this calculated matrix Table A.  all good.

I have another table - Table B - containing, per DISTRICT, the total area we WANT to achieve - lets call that TARGET AREA.

While there can be many burns per District in Table A; there's only one aspirational TARGET AREA, per District, in Table B.

 

Presumably, I need to create a relationship between the 2 tables, based on DISTRICT.

 

My question: How do I add the TARGET AREA (static) values (from Table B) per DISTRICT, to the matrix (calculated) summary in Table A?

 

Ultimately, trying to arrive at:
DISTRICT A is has a total (sum) PLANNED AREA of (say) 10,000 hectares.
DISTRICT A has a static TARGET AREA of (say) 20,000 ha.
Thus, DISTRICT A has done planning for 10,000 / 20,000 = 50% planned.

 

DISTRICTS ultimately belong to REGIONS via a heirarchy.

But that may not be relevant to the immediate challenge....

 

Any / all help would be 

BurnTotals.JPGCalculated PLANNED AREA totals - would like to add in the aspirational, static TARGET HECTARES

Above shows DISTRICTS, rolling up to REGIONS, with 2nd column successfully calculating how much we've PLANNED to burn - but need to display, alongside that, how much we ASPIRE to burn.  Ignore COUNTS, but it's the same problem / challenge - planned vs. targets.


 

1 ACCEPTED SOLUTION

Accepted Solutions
TeigeGao Member
Member

Re: Create a Calculated Column from Different Tables

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:

1111.png

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.

2222.png

The result will like below:

3333.png

Best Regards,

Teige

3 REPLIES 3
TeigeGao Member
Member

Re: Create a Calculated Column from Different Tables

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:

1111.png

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.

2222.png

The result will like below:

3333.png

Best Regards,

Teige

philpringuer Frequent Visitor
Frequent Visitor

Re: Create a Calculated Column from Different Tables

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 Frequent Visitor
Frequent Visitor

Re: Create a Calculated Column from Different Tables

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