Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Fixed line in line graph

Hi peeps   I have a link graph which is affected by an array of slicers however I do not want one of the lines to be changed when slicers are changes. My example is in the below. I want the "DPS Av...
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    In your fomula below, you add a extra symbol "{", it caused the error.

    Additonally, you should use the allexcept function, otherwise the fixed line value is the same for each date.

    If the Axis of the line chart is [Date], modify the formula like this:

    Fixed Line =
CALCULATE (
    [DSP Average],
    ALLEXCEPT ( 'Unscheduled Leave', 'Unscheduled Leave'[Date] )
)

    Get the correct result in my sample.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Apologies if I was not clear.

The measure I have above is the one I have duplicated. So Ill have both in the same graph but for one measure I want it to be affected (area) by the slicers and the other I do not want it to be affected. So when I use the Calculate[DPS Average] all()) for one measure it still is affected by the slicers.  

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    Sorry for the late back. How about using different measures separately, not to duplicate it. One using the original measure and another using the Fixed Line.

    Best Regards,
    Community Support Team _ kalyj

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Kalyj - unfortunately still doesnt work. Not duplicating the measure but duplicating the code for the measure but then creating a measure based on the code you have supplied. 

       

      So to explain I have the main measure of 

      Average Days Per FTE - rolling 12 Months =
      VAR STARTDATE =
          CALCULATE ( STARTOFMONTH('Unscheduled Leave'[Cal. year / month]) )
      RETURN
          CALCULATE (
              DIVIDE ( SUM ( 'Unscheduled Leave'[Actual Days]), SUM ( 'Employee Master'[FTE - Operational] ) ),
              DATESINPERIOD ( 'Report Date'[Date], STARTDATE, -12, MONTH)
              )*12
       
      Then I have duplicated this code for DPS Average. But to get DPS average I used your code 
      DPS Average = CALCULATE (
          [DPS Average Days Per FTE - Measure],
          ALLEXCEPT ( 'Report Date', 'Report Date'[Date] )
      ). 
       
      Unfortunately any time you change the slicers it changes everything
      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

        For example column in the slicer is [Category], try to modify the code:

        DPS Average =
CALCULATE ( [DPS Average Days Per FTE - Measure], ALL ( [Category] ) )

        Best Regards,
        Community Support Team _ kalyj