Forum Discussion

laciodrom_80's avatar
laciodrom_80
Helper IV
9 years ago
Solved

Reference lines on demand

Hi,

 

I've got a report with a line chart, I wish add to my report a reference line on demand: in particular I'd like to have a filter which specifies some reference lines (max - min - mean, etc.) so that when user clicks one of these the relative reference line is displayed into the chart, is it possible? 

 

Thanks in advance for any hint!

 

 

Need Help
Tips and Tricks
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    hi laciodrom_80

     

    First create a table with a column with Max, Min , Average ...Use this to show a slicer to select the calculate formula of the line.

     

    Next, create a measure to calc accord to slicer selected.

     

     

    LineCalc =
IF (
    VALUES ( Table1[Value] ) <> BLANK (),
    IF (
        HASONEVALUE ( Table2[LineType] ),
        SWITCH (
            VALUES ( Table2[LineType] ),
            "Max", CALCULATE ( MAX ( Table1[Value] ), ALLSELECTED ( Table1 ) ),
            "Min", CALCULATE ( MIN ( Table1[Value] ), ALLSELECTED ( Table1 ) ),
            "Average", CALCULATE ( AVERAGE ( Table1[Value] ),ALLSELECTED ( Table1 ) )
        )
    ),
    BLANK ()
)

     

8 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    hi laciodrom_80

     

    First create a table with a column with Max, Min , Average ...Use this to show a slicer to select the calculate formula of the line.

     

    Next, create a measure to calc accord to slicer selected.

     

     

    LineCalc =
IF (
    VALUES ( Table1[Value] ) <> BLANK (),
    IF (
        HASONEVALUE ( Table2[LineType] ),
        SWITCH (
            VALUES ( Table2[LineType] ),
            "Max", CALCULATE ( MAX ( Table1[Value] ), ALLSELECTED ( Table1 ) ),
            "Min", CALCULATE ( MIN ( Table1[Value] ), ALLSELECTED ( Table1 ) ),
            "Average", CALCULATE ( AVERAGE ( Table1[Value] ),ALLSELECTED ( Table1 ) )
        )
    ),
    BLANK ()
)

     

    • laciodrom_80's avatar
      laciodrom_80
      Helper IV
      9 years ago

      Hi  Vvelarde,

       

      thanks for your suggestion, only twoclarification:

       

      How are related the slicer selection and the LineCalc measure? Does Table2[LineType] in switch statement represent the current selection in the slicer?

       

      How can I add the LineCalc measure to my chart?

       

      Here my chart without reference lines

       

      I should add LineCalc measure to "Valori" field (Spot Series Values is the measure which draws the chart lines), but if I drag and drop LineCalc measure to "Valori" field it overwrites Spot Series Values :smileyfrustrated:

       

       Thanks a lot!

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        laciodrom_80

         

        How are related the slicer selection and the LineCalc measure? Does Table2[LineType] in switch statement represent the current selection in the slicer?

         

        Yes. able2[LineType] represent the current selection in slicer. The slicer is the column of tha table with the line types.

         

        How can I add the LineCalc measure to my chart?

         

        If you have a legend this doesnt' work. When you use a legenf only Works with 1 line.