Interaction between line chart and pie chart

[mvega07]

Hello,

I have a line graph in my report that shows the trend of sales over the last 12 months. In the same report I have a pie chart that shows the country where the sales came from, but the pie chart was showing as a default the total of the 12 months, while I needed it to show just the current one. To solve this I added a filter to the pie chart visual, using Top N = 1 for date (earliest date). Now my issue is that when I select a country in the pie chart, the line graph only shows the sales for the earliest date instead of showing the trend for that specific country.

Basically what I am trying to achieve is that by default, the pie chart shows only the data for the earliest date but if I select a piece of the pie (a specific country), the line graph should show the sales values for the whole 12 months.

Hope this makes sense, I have no idea how to word this issue to try and Google a solution (tried different ways, but nothing worked)

Thanks in advance!

[Anonymous · Accepted solution]

Hi @mvega07 ,

You can add filters to the code instead of the visual filters. Try using this MEASURE in the pie chart.

Current Month Sales =
CALCULATE (
    SUM ( 'FactSalesTable'[Sales] ),
    'Calendar'[Year] = YEAR ( TODAY () )
        && 'Calendar'[Month Number] = MONTH ( TODAY () )
)

If the current month refers to the month and year of the largest date in the date table, you can use this variable instead of TODAY().

VAR _max_date = CALCULATE(MAX('Calendar'[Date]),ALL())

 For the line chart, you can add a column to the date table.

then add a visual filter.

result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[mvega07]

Thanks, this works. The only thing is that when I then click on a specific data point in the line graph, the pie chart dissappears instead of showing how the sales per country where on the selected date. Do I have to modify the measure somehow to reflect this?

[mvega07]

Actually, just solved it. Updated my measure to this:

Current sales = 
VAR max_date = CALCULATE(MAX(Table_example[Date]),ALL())
RETURN
CALCULATE(SUM(Table_example[Sales]),FILTER(Table_example,Table_example[Date]=SELECTEDVALUE(Table_example[Date],max_date)) 
)

[amitchandak]

@mvega07 , Try new measures like for pie

new measure =
var _max = Minx(allselected(Date,Date[Date])
return
calculate( sum(Table[Value]), filter('Date', eomonth('Date'[Date],0) = eomonth(_max,0)))