Change column to subtract based on geographic heirarchy

[HassanI]

I am trying to create a visual which will show my "Gap to target value" at three layers in the visual. Region>Area>Country.

Here is my first attempt, the idea is to be able to drill though each layer and see the gap:

The visual gives the correct information at country level where Gap = Actual - Target, but at the area and regional level the visual is showing Gap = average of actual - average of target. I don't have the option to stop it summarizing. Okay so more background info:

This is a small snippit of the data. And here is what I need my visualisation to do:

1. Actual performance (at country level) = Quantity delivered/Quantity ordered. Then, Gap (country) = Actual performance - Market target.
2. Actual performance (at area level) = sum of Quantity delivered/sum of Quantity ordered. (i.e. sum both columns by area such as DACH) Then, Gap (area) = Actual performance - Area target.
3. Actual performance (at region level) = sum of Quantity delivered/sum of Quantity ordered. (i.e. sum both columns by region such as EMEA) Then, Gap (region) = Actual performance - region target.

Here is my PwBI relationships so far:

I have created seperate tables to store market, area, and regional targets, a table for the actual performance data, and a central market mapping table to relate the market, area, and regional names to some standardised names.

Is it possible to write 1 set of measures to show this in 1 visual (1 visual is key), right now i can do it but as 3 visuals.

[v-yuta-msft]

Hi HassanI,

Here is a workaround, please check if it can work. You can create a new column which contains value "Country level", "Region level" and "area level" in the Target table. Then you can drag the new column into legend field of chart.

Regards,

Jimmy Tao

[HassanI]

Hi Jimmy,

Are you suggesting instead of drill down through the geographic layers, we show each as a layer as a seperate bar colourised with legend?

This would mean I would be trying to show 37 bars on a chart at once? (32 countires, 5 areas, 1 region)

Hassan

[HassanI]

Hi,

Just realised you asked me to make it in the target table of which there are 3, not 1.