Have a chart that compares two values from a breakdown

[RogerSteinberg]

Hi guys,

Fiddle Data will Follow:

date	breakdown	value	pop
1/1/2019	country	germany	100
1/1/2019	country	france	80
1/1/2019	country	spain	70
1/1/2019	product	coke	20
1/1/2019	product	sprite	10
1/1/2019	product	fanta	5
1/2/2019	country	germany	99
1/2/2019	country	france	78
1/2/2019	country	spain	65
1/2/2019	product	coke	18
1/2/2019	product	sprite	12
1/2/2019	product	fanta	7
1/3/2019	country	germany	85
1/3/2019	country	france	91
1/3/2019	country	spain	74
1/3/2019	product	coke	24
1/3/2019	product	sprite	8
1/3/2019	product	fanta	18

I want to have the ability to chose a breakdown. Based on that selection compare two values in a line graph.

For example, if I select "country" in the breakdown column, I should be able to compare the following values: "germany", "france", spain".

If I'd rather compare products, then same thing, I'd like to view the difference between coke, sprite or fanta.

country > germany, france, spain

product > coke, sprite, fanta

The ideal finished product is to have a slicer for the breakdown column.

Two other slicers for Value 1 and Value 2.

The line graph would then showcase two lines : 1 being Value 1 and the second one Value 2.

I thought of making a measure that is filtered by Slicer1 (Value 1) and another measure filtered by Slicer 2 (Value 2).

[v-xuding-msft]

Hi @RogerSteinberg ,

You need to create 4 new tables and use the columns as slicers. I create a simple sample that you could reference to modify your report based on the actual requirement.

• Create new tables based on the values that need to be filtered

Note : There are no relatinships among the tables.

• Create measures

_Country = CALCULATE(SUM('Table'[pop]),FILTER('Table','Table'[breakdown] = "Country"))

Country 1 = 
var selectedvalue = SELECTEDVALUE('Country Value 1'[Value1])
var _value = CALCULATE(SUM('Table'[pop]),FILTER('Table','Table'[value] = selectedvalue))
return
IF(ISFILTERED('Country Value 1'[Value1]),_value,[_Country])

Country 2 = 
var selectedvalue = SELECTEDVALUE('Country Value 2'[Value2])
var _value = CALCULATE(SUM('Table'[pop]),FILTER('Table','Table'[value] = selectedvalue))
return
IF(ISFILTERED('Country Value 2'[Value2]),_value,[_Country])

For "Product", the formulas are same as "Country". Please see the attachment to get more details.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RogerSteinberg]

But what if i have dozens of breakdowns?

Also, you're providing two graphs , one for product and the other for country which wouldn't be sustainable with various breakdowns. Ideally as the breakdowns are being filtered, the values would change accordingly. Hence, one graph would be sufficient to compare values under a specific filtered breakdown.

I hope this makes it more clear.