Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
Yooooo it's great! Thank you so much, it works 🙂
Magic 😉
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
96 | |
84 | |
70 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |