Forum Discussion
Drilling down a chart using a slicer
Hey there,
I am displaying data in a stacked-column chart that can either be viewed by countries, or if we drill down, by cities.
I have drill-down activated on the chart, and so if I click on one country column it will drill-down and show the data for the cities of that country. That works well, I am happy 🙂
My question now: to make it easier for people reading & playing these reports, I'm trying to make a slicer that would do basically the same. My idea would be to have a slicer that lists all the countries, and as the user clicks on a country the chart would refresh and only show the cities.
Hi julienvdc ,
Please check:
Sample data:
Country City Product Value Country1 City1-1 Product1 1 Country1 City1-2 Product1 2 Country1 City1-3 Product1 3 Country1 City1-4 Product1 4 Country2 City2-1 Product1 5 Country2 City2-2 Product1 6 Country2 City2-3 Product1 7 Country2 City2-4 Product1 8 Country3 City3-1 Product1 9 Country3 City3-2 Product1 10 Country3 City3-3 Product1 11 Country3 City3-4 Product1 12 Country1 City1-1 Product2 12 Country1 City1-2 Product2 11 Country1 City1-3 Product2 10 Country1 City1-4 Product2 9 Country2 City2-1 Product2 8 Country2 City2-2 Product2 7 Country2 City2-3 Product2 6 Country2 City2-4 Product2 5 Country3 City3-1 Product2 4 Country3 City3-2 Product2 3 Country3 City3-3 Product2 2 Country3 City3-4 Product2 1 1. Create a x-axis table.
X axis = VAR Country_ = DISTINCT('Table'[Country]) VAR City_ = DISTINCT('Table'[City]) RETURN UNION(Country_,City_)2. Create relationships between "Table" and "X axis" table.
3. Create a measure.
Measure = IF ( ISFILTERED ( 'Table'[Country] ), CALCULATE ( SUM ( 'Table'[Value] ), USERELATIONSHIP ( 'Table'[City], 'X axis'[Country] ) ), SUM ( 'Table'[Value] ) )4. Create a Stacked column chart visual.
5. Test.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi julienvdc ,
Please check:
Sample data:
Country City Product Value Country1 City1-1 Product1 1 Country1 City1-2 Product1 2 Country1 City1-3 Product1 3 Country1 City1-4 Product1 4 Country2 City2-1 Product1 5 Country2 City2-2 Product1 6 Country2 City2-3 Product1 7 Country2 City2-4 Product1 8 Country3 City3-1 Product1 9 Country3 City3-2 Product1 10 Country3 City3-3 Product1 11 Country3 City3-4 Product1 12 Country1 City1-1 Product2 12 Country1 City1-2 Product2 11 Country1 City1-3 Product2 10 Country1 City1-4 Product2 9 Country2 City2-1 Product2 8 Country2 City2-2 Product2 7 Country2 City2-3 Product2 6 Country2 City2-4 Product2 5 Country3 City3-1 Product2 4 Country3 City3-2 Product2 3 Country3 City3-3 Product2 2 Country3 City3-4 Product2 1 1. Create a x-axis table.
X axis = VAR Country_ = DISTINCT('Table'[Country]) VAR City_ = DISTINCT('Table'[City]) RETURN UNION(Country_,City_)2. Create relationships between "Table" and "X axis" table.
3. Create a measure.
Measure = IF ( ISFILTERED ( 'Table'[Country] ), CALCULATE ( SUM ( 'Table'[Value] ), USERELATIONSHIP ( 'Table'[City], 'X axis'[Country] ) ), SUM ( 'Table'[Value] ) )4. Create a Stacked column chart visual.
5. Test.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Icey ,
Amazing! From the look of that last .gif that is what I am looking for!
I am just not too sure I understand what that measure is doing. Could you describe it to me?
I forgot to mention that my data is organized into 2 tables:
There is the product table...
EX:
Product ID Brand Location ID 1 Lenovo 30 3 Dell 34 and the there is the location table which mixes countries and cities
EX:
Location ID City Country 30 Athens Greece 34 Paris France 1 France Does that make sense?
Hi julienvdc ,
I am just not too sure I understand what that measure is doing. Could you describe it to me?
Measure =
IF (
ISFILTERED ( 'Table'[Country] ),----------------Judge whether a country is selected with the slicer.
CALCULATE (
SUM ( 'Table'[Value] ),
USERELATIONSHIP ( 'Table'[City], 'X axis'[Country] )
),--------------------------If one country is selected, calculate value based on the inactive relationship('X axis'[Country] -> 'Table'[City]).
SUM ( 'Table'[Value] )-----------if no country is selected, calculate value based on the active relationship ('X axis'[Country] -> 'Table'[Country]).
)I forgot to mention that my data is organized into 2 tables:
There is the product table...
EX:
Product ID Brand Location ID 1 Lenovo 30 3 Dell 34 and the there is the location table which mixes countries and cities
EX:
Location ID City Country 30 Athens Greece 34 Paris France 1 France With right relationships, this doesn't make any difference.
Do I explain clearly?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
