topic Re: Average Line for Line and Stack Chart in DAX Commands and Tips

Average Line for Line and Stack Chart

ranis1227 — Fri, 12 May 2023 12:20:34 GMT

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.

Re: Average Line for Line and Stack Chart

Anonymous — Mon, 15 May 2023 05:14:58 GMT

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.

Re: Average Line for Line and Stack Chart

ranis1227 — Mon, 15 May 2023 12:29:44 GMT

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?

Re: Average Line for Line and Stack Chart

Anonymous — Tue, 16 May 2023 02:48:43 GMT

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.

Re: Average Line for Line and Stack Chart

ranis1227 — Wed, 17 May 2023 14:08:13 GMT

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.