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 been the case?

 

So I tried creating an average line and could not figure it out in dax.  Here are my axis: 

X-axis = Date

Column y-axis = Volume of Patients

Line y-axis = Note type compliance (basically a sum of patients with a notetype/Volume of Patients)

 

I am trying to create an average line for the note type compliance. So basically an average of a percentage.  I currently have it set as a constant at the moment. I need it to move with my date slicer. Any help will be greatly appreciated.   

4 Replies

  • 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.

         

  • ranis1227's avatar
    ranis1227
    Helper I
    3 years ago

    So the issue is with Average function not able to pick up the measure of: 

    AVERAGE ( 'Incidental Findings Note'[Incidental Findings Note Type Compliance] )

     

    Here is the error:  The AVERAGE function only accepts a column reference as an argument.

     

    The breakdown of the measures are as below:

     

    Incidental Findings Note Type Compliance = [Sum of Discharge Patients w Note Type IF]/[Sum of Incidental Findings MRN]


    Sum of Discharge Patients w Note Type IF = CALCULATE(COUNT('Distinct MRN for Incident Findings Note Type'[MRN]), 'Distinct MRN for Incident Findings Note Type'[Incidental Both] = 1)

     

    Sum of Incidental Findings MRN = DISTINCTCOUNT('Table1 (2)'[MRN])

     

     

    I have tried AVERAGEX to capture the measure and still could not get the correct average.  Let me know if that helps. I will try to provide a sample pbix file. The issue is private health information data in the file.