Forum Discussion
Interaction between line chart and pie chart
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!
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 TeamIf 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
4 Replies
- amitchandak
Super User
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))) 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 TeamIf 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
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?
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)) )
