Forum Discussion
Helper IIIDrilling down a chart using a slicer
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.
Helper IIIHey 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?
Community SupportHi 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.
- julienvdc
Yooooo it's great! Thank you so much, it works 🙂
Magic 😉