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.) ...
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 ()
)

     

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.