cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Mixed graph: bar and line, but with different behaviour

Hi all,

 

I am new at Power BI but I want to built a mixed graph: a line and a bar.

But the line should not react on the slicer, and the bar should react on the slicer.

See my example below:

This is my table with info

Week codeIn_base
202046       120.800
202045       119.050
202044       117.300
202043       115.550
202042       113.800
202041       112.050
202040       110.300
202039       105.000
202038       100.000
202037         95.000
202036         90.000
202035         80.000
202034

         75.000

 

And I would like to built the graph as below. So when I use the slicer on week code, the line should stay as is, but the bar should change:

 

slicer on weekcode = 202046.PNGslicer on weekcode = 202041.PNG

 
 

Can anyone help me out on this?

 

Thanks very much!

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi, @Sander1977 

As mentioned by @amitchandak ,you need to create a dimension table of week code and establish a relationship with the fact table

Slicer Table = DISTINCT(SELECTCOLUMNS('Table',"Week code",'Table'[Week code]))

Create the following  measure and  aplply it to the "line value" of chart.

Bar value = CALCULATE(SUM('Table'[In_base]),ALLEXCEPT('Table','Table'[Week code]))

The result will show as below:

 30.png

 

Best Regards,
Community Support Team _ Eason

View solution in original post

3 REPLIES 3
Community Support
Community Support

Hi, @Sander1977 

As mentioned by @amitchandak ,you need to create a dimension table of week code and establish a relationship with the fact table

Slicer Table = DISTINCT(SELECTCOLUMNS('Table',"Week code",'Table'[Week code]))

Create the following  measure and  aplply it to the "line value" of chart.

Bar value = CALCULATE(SUM('Table'[In_base]),ALLEXCEPT('Table','Table'[Week code]))

The result will show as below:

 30.png

 

Best Regards,
Community Support Team _ Eason

View solution in original post

Continued Contributor
Continued Contributor

Please consider this solution and leave feedback  …

Try using 2 calendar tables.

One for the sliders and one for the graph x axis.

Then create 2 measures

Block graph qty =

SUM(Sales[Qty])


Line graph qty =

CALCULATE(SUM(Sales[Qty]),

ALL(SlicerCalendar)

)

 

Click here for example 

Super User IV
Super User IV

@Sander1977 , the best way to do is to have an independent table with Week and use that in the slicer. Create a measure (for the bar) that uses this filter

measure = calculate(sum(Table[in_base]), filter(Table, Table[Week code] in allselected(week[week])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors