Add target line for each filter in slicer

[Anonymous]

Hi all,

Want to ask for below chart. For chart A, I add a constant line for overall machine target (79.5%)

But when I choose certain machine (eg: 310 EA Machine) the target should be different, but as you can see below the constant line remain (79.5%) which is wrong. 

Chart A

Chart B

I know constant line are no possible for my solution. Do you have suggestion how can I do a target line based on each machine (filter).

Regards,

Nuha

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Slicer_Machine =
DISTINCT('Table'[Machine])

Slicer_Yearmonth =
DISTINCT('Table'[Year & Month])

2. Create measure.

select_value =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
DIVIDE(
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month&&'Table'[Machine]=_group)),
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month)))

month_amount =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date].[Month]=_month)
)

Flag =
var _year=SELECTEDVALUE('Slicer_Yearmonth'[Year & Month])
var _group=SELECTEDVALUE('Slicer_Machine'[Machine])
var _month=CALCULATE(MAX('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Year & Month]=_year))
return
IF(
    FORMAT(MAX('Table'[Date]),"mmmm")=_month,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Machine == A

Machine == B

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

How should the target be calculated?  What is the logic of that calculation?

[Anonymous]

Hi @Ashish_Mathur ,

There is no calculaton for target. We just input the target based on virtual discussion.

For example,

Machine	Target	Result for May22 (202205)
310 EA Machine	94.6%	80.99%

Regards,

Nuha

[amitchandak]

@Anonymous , Then you need to create a measure and use that as a constant line or create a clustered bar visual

Switch (true() ,

not(isfiltered(Table[Machine])) , 79.5 ,

max(Table[Machine]) = "310 EA Machine", 70.0

)

Add others as per need

[Anonymous]

For clustered bar visual, how to combine stacked column chart with line chart?