Hello,
I am using the below table which has the following columns (has been filtered to limit rows). The data_level column determins whether the data is national (national_1), country (national_3), region (region), county (county), local authority (local_authority).
What I need to create:
- Bar chart which can be drilled down from the highest level 'national_1', 'national_3', 'region', 'county' and to the lowest 'local_authority'
- At each level of the drill down I want the data to point to the sum (numeric_value) of that data level e.g. if the chart is drilled down to region level data, then I want the total numeric_value for all regions under that country (national_3)
- I want to be able to use a slicer which will also drill the data down to the correct data level e.g. if I select Kent in the 'County' slicer then I would want the visual to filter to 'county' level data
- The data cannot be summed from the lowest level being local_authority as each data level pulls from a different data source.
| national_1 | national_3 | region | county | local_authority | data_level | numeric_value |
| United Kingdom | England | | | | National_3 | 453400066 |
| United Kingdom | England | South East | | | Region | 179523 |
| United Kingdom | England | South East | Kent | | County | 893 |
| United Kingdom | England | South East | Kent | Thanet | Local_Authority | 68 |
| United Kingdom | England | South East | Kent | Swale | Local_Authority | 89 |
| United Kingdom | England | South East | Kent | Maidstone | Local_Authority | 65 |
| United Kingdom | England | South East | Kent | Canterbury | Local_Authority | 65 |
I have been stuck on this issue all week and would love to some help to conquer this issue, thank you in advance! 🙂
The below is what I have tried and it nearly works but doesn't like multiple regions being selected for the same country etc.
= SWITCH(TRUE()
----COUNTRY
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" && HASONEVALUE(Consolidated_LLMIT[national_3]) && HASONEVALUE(Consolidated_LLMIT[region]) = FALSE(), _VolumeCountry
----REGION
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" && HASONEVALUE(Consolidated_LLMIT[region]) && HASONEVALUE(Consolidated_LLMIT[county]) = FALSE(), _VolumeRegion
----COUNTY
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" && HASONEVALUE(Consolidated_LLMIT[county]) && HASONEVALUE(Consolidated_LLMIT[local_authority]) = FALSE(), _VolumeCty
----LOCAL AUTHORITY
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" && HASONEVALUE(Consolidated_LLMIT[local_authority]), _VolumeLocalAu
----NATIONAL
, SELECTEDVALUE('Dynamic Table'[Name]) = "Volume" , _VolumeNational
,0)
