Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-easonf-msft
Community Support
Community Support

Hi, @Anonymous 

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
v-easonf-msft
Community Support
Community Support

Hi, @Anonymous 

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

speedramps
Super User
Super User

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 

amitchandak
Super User
Super User

@Anonymous , 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])))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.