Forum Discussion

ranis1227's avatar
ranis1227
Helper I
3 years ago

Average Line for Line and Stack Chart

Hello, I am on the latest release of Power Bi and it looks like the Average line is not available in the analytics windows of the visualization for Line and Stacked Column Chart.  Has this always be...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi ranis1227 ,

If so, you can create a measure.

If it is a single selection slicer, please have a try.

measure =
VAR _1 =
    SELECTEDVALUE ( table[date] )
RETURN
    CALCULATE (
        AVERAGE ( table[value] ),
        FILTER ( ALL ( table ), table[date] >= _1 )
    )

If it is a date range slicer, please have a try.

measure =
VAR _1 =
    MAXX ( ALLSELECTED ( table ), table[date] )
VAR _2 =
    MINX ( ALLSELECTED ( table ), table[date] )
RETURN
    CALCULATE (
        AVERAGE ( table[value] ),
        FILTER ( ALL ( table ), table[date] <= _1 && table[date] >= _2 )
    )

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

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

ranis1227's avatar
ranis1227
Helper I
3 years ago

Anonymous  thank you for the suggestion. However, I am still getting the same issue of Column 'Incidental Findings Note Type Compliance' in table 'Incidental Findings Note' cannot be found or may not be used in this expression.  Here is the measure I used:

 

measure Average =
VAR _1 =
    MAXX ( ALLSELECTED ('Table1 (2)'), 'Table1 (2)'[Date Signed] )
VAR _2 =
    MINX ( ALLSELECTED ( 'Table1 (2)' ), 'Table1 (2)'[Date Signed] )
RETURN
    CALCULATE (
        AVERAGE('Incidental Findings Note'[Incidental Findings Note Type Compliance]),
        FILTER ( ALL('Table1 (2)'), 'Table1 (2)'[Date Signed] <= _1 && 'Table1 (2)'[Date Signed] >= _2 )
    )
 
The main issue is the incidental note type compliance is a percentage. Is this possible? 
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ranis1227 ,

    It seems that there are 2 tables!

    Please change the measure. Put the 'table1(2)' date  into the slicer.

    measureAverage =
VAR _1 =
    MAXX ( ALLSELECTED ( 'Table1 (2)' ), 'Table1 (2)'[Date Signed] )
VAR _2 =
    MINX ( ALLSELECTED ( 'Table1 (2)' ), 'Table1 (2)'[Date Signed] )
RETURN
    CALCULATE (
        AVERAGE ( 'Incidental Findings Note'[Incidental Findings Note Type Compliance] ),
        FILTER (
            ALL ( 'Incidental Findings Note' ),
            'Incidental Findings Note'[date] <= _1
                && 'Incidental Findings Note'[date] >= _2
        )
    )

    And make sure there is no relationship between the tables. 

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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