Average line for a line in colum/line chart

[rpinxt]

I have a graph with columns and a line:

Now I wanted to have an average for this green line. So this would be a horizontal line all values per period divided by the number of periods with a value.

Tried it with quick measures and it made me this :

DPMO average per Period =

AVERAGEX(KEEPFILTERS(VALUES('dimDate'[Period])), CALCULATE([DPMO]))

But the restult is exactly the same line as the green line which you can (barely) see by the white labels.

How would I make a straight horizontal avarage line? A line that has the same amount for every period.

[rpinxt]

Bottom line summary the DPMO average per Period is actually correct on a total level.

So if somebody knows how I can have this total on every line it would already be solved I guess:

DPMO average per Period =

AVERAGEX(KEEPFILTERS(VALUES('dimDate'[Period])), CALCULATE([DPMO]))

But as a workaround I was already thinking this :

Note: I put hardcoded the values in Test fields!

Test is the total of DPMO. No clue why on the total line it gives 1562 but if you count it in excel it is 13900.

Then Test 2 is the number of periods in the visual which is 13.

And finally a simple division would give me 1069 on every line.

But how would I get to the 13900 and 13?

[rpinxt]

Thanks @Data-estDog but I cannot see how I would put my own data in your solution.

My data is fairly simple.

Maybe this table representation makes it clearer:

So it is just an autocalendar generating periods.

And 1 measure returning amount.

The average now should look like:

And idealy it should only average when DPMO is greater than 0

*Edit: To add to this 1069 is the average but it does not show it on every line in the visual.

[Data-estDog]

Data helps. 
Also, your data set is not as simple as you say. You are using a date dim, and DPMO is a measure which is why you used averagex to push it accross periods. 
So to simulate that:

Replace SUM(DPMO) with your measure.

Don't forget to mark as solution and give a thumbs up!

[rpinxt]

Well....there is not really a DPMO table.

This is the measure

DPMO = DIVIDE([Complaints#],[DPMO_ship],0) * 1000000

It is not a field from a table.

[Data-estDog]

That's how I imagined it. 

Replace "DPMOMeasure" with your DPMO. Replace DPMOTable with whatever table Complaints# or DPMO_Ship come from. 

AvgDPMO = CALCULATE(AVERAGEX(DPMOTable, [DPMOMeasure]), ALLSELECTED(DPMOTable))

[rpinxt]

Sorry @Data-estDog but no luck with that.

I have a sample file here:

https://drive.google.com/file/d/1kFqaEQ0O2nE7hMAAFHbs4rUB0dsj8JEl/view?usp=drive_link

Maybe you can see for yourself there.

[v-cgao-msft · Accepted solution]

Hi @rpinxt ,

Please try this mesure:

DPMO average per Period 1 = IF([DPMO]>=0 && NOT ISBLANK([DPMO]),CALCULATE(AVERAGEX(VALUES('dimDate'[Period]),[DPMO]),ALLSELECTED()))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[rpinxt]

This is great @v-cgao-msft ! 😄

Thanks a lot.

Maybe a stretch but the total is 13900 divided by 13 gives 1069.

What if it should not take the zeros in the average? So actually divide by 11 instead of 13.

Guess you would need to make it that DPMO not gives 0 but blank?

[Data-estDog]

Here's an example. It changes based on periods selected:
Avg resolved to closed =
CALCULATE (
    AVERAGEX (
        Sheet2,
        DATEDIFF ( Sheet2[resolved_date], Sheet2[Closed Date], MINUTE ) /*my measure*/
    ),
    ALLSELECTED ( Sheet2 )
)

If you want average accross all periods reguardless of filters, try: 

Avg resolved to closed2 =
AVERAGEX (
ALL ( Sheet2 ),
DATEDIFF ( Sheet2[resolved_date], Sheet2[Closed Date], MINUTE )
)