Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate the weighted average

Hi,

 

I'm trying to calculated the weighted average working days becasue we have subregions under regions and I have two questions:

 

(1) How can I calculate total working days for each subregion?

My data would look sth like this:

  • For each sub region, I need to calculate the number of working days , excluding if weekday is Sat or Sun (showing 6 or 7 under weekday column) as well as if holiday is showing 1.
    SubregionDatesweekdayholiday
    a1/1/202150
    a1/2/202160
    a1/3/202170
    a1/4/202111
    a1/5/202120
    a1/6/202130
    a1/7/202140
    b1/8/202150
    b1/9/202160

I am imagining eventually I will be abe to use power BI slicer or parameter to define the end date, so there is no end date column here. And I don't know how to do that. (Creating a measure on a measure???)

The output should be (excluding both holiday and weekend):

  •  total days
    a4
    b0

 

(2) How can I aggregate it and do a weighted average based on region?

After getting the working days...

Combining the previous, the data would look sth like this:

  • sub regions are unique and each belongs to a region
  • each weighted factor equals to total days worked within each sub region times the total amount within each sub region (ex. 10*100=100)
    RegionSubregionTotal DaysTotal AmountWeighted Factor 
    AAa101001000
    AAb115005500
    CCc114504950
    DDd109509500
    CCe96105490
    BBf83102480

Eventually I'm trying to get the weighted total days of each region:

  • weighted total amount is summing up the total amount of each sub region (ex. 600= 100(a) +500(b))
  • average weighted amount is summing up the weighted factor of each sub region ex. 6500= 1000(a) +5500(b))
  • I want to calculate the weighted total days by dividing average weighted factor by weights total amount (ex. 10.83333= 6500/600)
     wieghtes total amountaverage weighted factorweighted total days
    AA600650010.83333333
    BB31024808
    CC1060104409.849056604
    DD950950010

Does anyone have anyidea how I can do this via DAX ?

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

 

I created a sample and some measures then got the following results.

vangzhengmsft_1-1634015064479.png

Measures:

__TotalDays = CALCULATE(COUNT(Question2[Subregion]),'Question2'[Weekday] in {1,2,3,4,5}&&'Question2'[Holiday]<>1)
__TotalAmount = SUM('Question2'[Amount])
__Weighted Factor = [__TotalAmount]*[__TotalDays]
__Weighted Factor_2 = 
var _t=SUMMARIZE('Question2',Question2[Region],Question2[Subregion],"Weighted Factor",[__Weighted Factor])
var _if=IF(ISINSCOPE(Question2[Region]),IF(ISINSCOPE(Question2[Subregion]),[__Weighted Factor],SUMX(_t,[Weighted Factor])),SUMX(_t,[Weighted Factor]))
return _if
__weighted total days = DIVIDE([__Weighted Factor_2],[__TotalAmount])

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-angzheng-msft
Community Support
Community Support

Hi, @Anonymous 

 

I created a sample and some measures then got the following results.

vangzhengmsft_1-1634015064479.png

Measures:

__TotalDays = CALCULATE(COUNT(Question2[Subregion]),'Question2'[Weekday] in {1,2,3,4,5}&&'Question2'[Holiday]<>1)
__TotalAmount = SUM('Question2'[Amount])
__Weighted Factor = [__TotalAmount]*[__TotalDays]
__Weighted Factor_2 = 
var _t=SUMMARIZE('Question2',Question2[Region],Question2[Subregion],"Weighted Factor",[__Weighted Factor])
var _if=IF(ISINSCOPE(Question2[Region]),IF(ISINSCOPE(Question2[Subregion]),[__Weighted Factor],SUMX(_t,[Weighted Factor])),SUMX(_t,[Weighted Factor]))
return _if
__weighted total days = DIVIDE([__Weighted Factor_2],[__TotalAmount])

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k
Super User
Super User

@Anonymous try this:

 

DIVIDE ( [Weighted Factors], CALCULATE ( [Weighted Factors], REMOVEFILTERS ( YourTable[Sub Region Column] ) ) )

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

AlexisOlson
Super User
Super User

If you have the [Total Days] and [Total Amount] columns, then the weighted average is

AvgDays =
DIVIDE (
    SUMX ( Table1, Table1[Total Days] * Table1[Total Amount] ),
    SUMX ( Table1, Table1[Total Amount] )
)

AlexisOlson_0-1633719955549.png

Anonymous
Not applicable

Hi Alexis,

 

The quesiton is I started to calculate total days & amount based on subregoins, and eventually I would need to divide based on regions.

The original dataset looks like this:

DateRegionSubRegionTotal AmountHolidayWeekday
2021/11/1AAa5001
2021/11/2AAa502
2021/11/3AAa9005
2021/11/4AAa6004
2021/11/1AAb4001
2021/11/2AAb6002
....     

 

So I did a measure based on subregion: 

Calculating the working days, amount, and weighted factor by typing: 

WorkingDays = CALCULATE(COUNTROWS(Data)-sum(Data[Holiday]),Data[Weekday]<6)
Sum of total amount = sum(Data[Total Amount])
Weighted Facotrs = [Sum of total amount]*[WorkingDays]
And now I get something like this when I visualize it in a matrix(not the format of my original data) if I view via year_month:
 
YearSum of total amountWorkingDaysWeighted Facotrs
2021   
  Nov   
     AA305214300
        a205101000
        b100113300
       ....   
    BB11504015800
        c500168000
        d400124800
        e250123000
     
I want to find a way to divide the measures based on Region. Now if I divide its still gonna divide by subregion

Side note: It's probably worth adding an [IsWorkingDay] calculated column

IsWorkingDay = ( Data[Holiday] = 0 ) && ( Data[Weekday] < 6 )

to your table so that you can write [WorkingDays] more simply

WorkingDays = CALCULATE ( COUNTROWS ( Data ), Data[IsWorkingDay] )

 Doing any other logic with working days becomes simpler too.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.