Heirarchies - summing 'up' works for numbers - but NOT for percentages

[philpringuer]

Hi Team - any help appreciated.

I've a tonne of raw data pertaining to planned burning - around 2,000 rows of data; one row per burn.

We plan to burn in 'zones' - e.g. for Asset Protection, or Bushfire Moderation, or Landscape Management.

Each Burn belongs to one of about 20 Districts (physical land masses - like a Suburb).

Each District belongs to one of five Regions (larger land masses - like States).

Per District, per Zone, we aspire to burn a certain amount - called a Program Area.

Per District, per Zone, we actually burn a (likely) lesser amount - the Area Treated.

So we have a heiarchy - Region --> District --> Zone.

I've managed to create a matrix, with that heirarchy as ROW headings.

And columns, summarising:

• Program Area and
• Area Treated and 
• Total Area as % of Program (dividing one by the other).

Program Area and Area Treated  roll-up in the heirarchy as hoped, by summing: which is correct.

Total Area as % of Program also sums: which is NOT correct.

e.g. 4% achievement for Zone 1, and 6% achievement for Zone 2, does NOT equal (4+6)=10% for the District!

Average is not appropriate either ... avg(4,6)=5%

Is my heirarchy approach doomed when trying to determine the % achievement at each level?
i.e. is it doomed to try to show total counts, and total %s, in the one heirarchical matrix?

Or is my approach wrong?

Counts work...

Counts roll-up finePercentage achievements at Zone level work fine ... but roll-up totals aren't appropriate / are inaccurate

I'm analysing data held in calculated COLUMNS, rather than using MEASURES.
Perhaps that's at the heart of my problem (?)

phil

[v-shex-msft]

Hi @philpringuer ,

It seems like a common measure total level calculation issue, can you please share some sample data with expected result to help us clarify your requirement and do test on these sample data?

Notice: do mask on sensitive data.

Regards,

Xiaoxin Sheng

[philpringuer · Accepted solution]

Hi Xiaoxin - I lack the English language skills to summarise my problem well.

Hold off helping for now - i'll do a bit more study before bothering you and the community.

Thanks for your offer of support!

phil