slicer - Line chart with selected value and overall value

[devika]

Hi

Thanks for your time.

I'm creating a line graph showing the total sales and also by region in the same graph.

• Total sales by year should always be displayed.
• When a user doesn't select any region, only show the total by year.
• When a user select a region/regions,show the regions as a separate line and overall total(all the regions total not only selected regions total).
• Don't show "total" as an option in the slicer.

Any advice is much appreciated.

Year	Region	Sales
2018	A	100
2018	B	200
2018	C	300
2019	A	400
2019	B	500
2019	C	600
2020	A	700
2020	B	800

[Shreeram04]

Hi @devika ,

Please create the two below-mentioned measurements and add them to your line chart.

1. Total Sales = SUM('Table'[Sales])

2. Total Sales Selected =

IF (

    ISFILTERED('Table'[Region]),

    CALCULATE(SUM('Table'[Sales]), ALL('Table')),

    SUM('Table'[Sales])

)

While select value in the filter it show both total and selected region 

 when nothing is selected it shows the total value only

If this post helps, then please consider Accept it as the solution 

Thanks,

Hari

[devika]

@Shreeram04 - Thanks for your time.sorry not really,

  1. I need to show 600,1500,1500 for total by year (always)

 2.  If region A is selected then 100,400,700 ( as a single line for Region A by Year)

3.   If region A and B are selected then 100,400,700 ( as a single line for Region A by Year )  200,500,800 (as a single line for Region B by Year)

[devika]

Hi - I am taking a step back to show the grand total and just changed the slicer to single select, so it is working fine in this case.

Can anyone please advise if multiselection in the slicer is possible and also show the regions and  lines/plots separately ?

I have seen the post to create parameter but this won't work in the scenario as I need to produce three different graphs - Profit, Loss, Sales interacting to one Region slicer

 

 Measures: (similarly created for Profit)

Total_Loss_line = CALCULATE(sum('Table'[Loss]),All('Table'[Region]))

Total_Loss_Selected =

IF (

    ISFILTERED('Table'[Region]),

    CALCULATE(SUM('Table'[Loss])),

    SUM('Table'[Loss])

)