cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Have a chart that compares two values from a breakdown

Hi guys,

 

Fiddle Data will Follow:

datebreakdownvaluepop
1/1/2019countrygermany100
1/1/2019countryfrance80
1/1/2019countryspain70
1/1/2019productcoke20
1/1/2019productsprite10
1/1/2019productfanta5
1/2/2019countrygermany99
1/2/2019countryfrance78
1/2/2019countryspain65
1/2/2019productcoke18
1/2/2019productsprite12
1/2/2019productfanta7
1/3/2019countrygermany85
1/3/2019countryfrance91
1/3/2019countryspain74
1/3/2019productcoke24
1/3/2019productsprite8
1/3/2019productfanta18

 

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).

2020-02-06 11_10_22-Untitled - Power BI Desktop.png

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Have a chart that compares two values from a breakdown

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.

3.PNG

  • 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.

2.PNG2.PNG

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.

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.
Highlighted
Helper V
Helper V

Re: Have a chart that compares two values from a breakdown

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.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors