Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rpinxt
Impactful Individual
Impactful Individual

AVG for all selected but reset by year

Example this tabel :

rpinxt_0-1701948595774.png

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

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.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
rpinxt
Impactful Individual
Impactful Individual

Can the link be accessed??

 

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

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.


Go to My LinkedIn Page


rpinxt
Impactful Individual
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))
rpinxt
Impactful Individual
Impactful Individual

Well @Jihwan_Kim  I tried the same also for UCL :

rpinxt_0-1702043299241.png

 

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! 😉 )

 

Jihwan_Kim
Super User
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.


Go to My LinkedIn Page


rpinxt
Impactful Individual
Impactful Individual

Thanks @Jihwan_Kim 

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

rpinxt_0-1701951269909.png

 

I have put a sample file on google drive.

You can access it with this link:

https://drive.google.com/file/d/1kIF5yJ2bTa1_pXzs8VSBDdmMDso38n4n/view?usp=sharing

Helpful resources

Announcements
Fabric Community Conference

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.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.