cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Impactful Individual

## AVG for all selected but reset by year

Example this tabel :

You see there are periods without complaints.

To get overal a DMPO_avg I used :

DPMO_avg = CALCULATE(AVERAGEX(VALUES('dimDate'[Period]),[DPMOco]),ALLSELECTED())

But of course the average of 2022 should be different from the 2023 average.
How would I do that?

And for UCL something similar however that calculation is more complicated.
The Help field :
UCL_Cntry_help =
IF([Complaints#] < 1,
BLANK(),
CALCULATE(SUM(UCL[Qty]),UCL[Country] = SELECTEDVALUE('Case'[Country__c]),ALL(dimDate[Period]),YEAR(dimDate[Date])=CONVERT(RIGHT((SELECTEDVALUE(dimDate[Period])),4),INTEGER))
)

This gives by year the correct values. So 2022 is 4733 and 2023 4842.
Then I used average to get that number in every period (to create a horizontal line in the graph):
UCL_Cntry = CALCULATE(AVERAGEX(VALUES('dimDate'[Period]),[UCL_Cntry_help]),ALLSELECTED())

However as you see a similar problem as it takes the average of both years and need to break that down by year.

As average formulas are similar is there a way to make both break down by year.
So still for all selected by reset on year basis.

1 ACCEPTED SOLUTION
Super User

Can you try the below if it suits your requirement?

``````Test =
AVERAGEX (
FILTER (
ALL ( 'dimDate'[Period], dimDate[YM],dimDate[Year] ),
dimDate[Year] = MAX(dimDate[Year])
),
[DPMOco]
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

6 REPLIES 6
Impactful Individual

@Jihwan_Kim please let me know if you are not able to access the example file

Super User

Can you try the below if it suits your requirement?

``````Test =
AVERAGEX (
FILTER (
ALL ( 'dimDate'[Period], dimDate[YM],dimDate[Year] ),
dimDate[Year] = MAX(dimDate[Year])
),
[DPMOco]
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Impactful Individual

Yes now I get 2 different averages for the years.

Would that also work for the UCL_Cntry field?
Talking about that field would you know a less complex way then:

UCL_Cntry = CALCULATE(SUM(UCL[Qty]),UCL[Country] = SELECTEDVALUE('Case'[Country__c]),ALL(dimDate[Period]),YEAR(dimDate[Date])=CONVERT(RIGHT((SELECTEDVALUE(dimDate[Period])),4),INTEGER))
Impactful Individual

Well @Jihwan_Kim  I tried the same also for UCL :

The number are correct for test2 but you see there are gaps.

So I thought it would not work.

However strange enough in a line graph it just one sollid line so actually all ok 😄

Thanks a lot! (Bedankt! 😉 )

Super User

Hi,

If you can provide the sample pbix file's link here, it will be very much helpful.

But for now, please try the below if it suits your requirement.

``````DPMO_avg =
VAR _currentyear =
RIGHT ( MAX ( 'dimDate'[Period] ), 4 )
RETURN
AVERAGEX (
FILTER (
ALL ( 'dimDate'[Period] ),
RIGHT ( 'dimDate'[Period], 4 ) = _currentyear
),
[DPMOco]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Impactful Individual

Thanks @Jihwan_Kim

Unfortunately it gives the same values then as the normal DPMO:

I have put a sample file on google drive.

You can access it with this link:

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.