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!

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

laciodrom_80

 

Just upload a simple .pbix file here for reference which follow the solution provided by Vvelarde.

If you have legend in your line chart, as Vvelarde said, you cannot have more than one value in the Values field now. Please vote this idea here.

 

Best Regards,

Herbert