Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Subregion | Dates | weekday | holiday |
a | 1/1/2021 | 5 | 0 |
a | 1/2/2021 | 6 | 0 |
a | 1/3/2021 | 7 | 0 |
a | 1/4/2021 | 1 | 1 |
a | 1/5/2021 | 2 | 0 |
a | 1/6/2021 | 3 | 0 |
a | 1/7/2021 | 4 | 0 |
b | 1/8/2021 | 5 | 0 |
b | 1/9/2021 | 6 | 0 |
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 | |
a | 4 |
b | 0 |
(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:
Region | Subregion | Total Days | Total Amount | Weighted Factor |
AA | a | 10 | 100 | 1000 |
AA | b | 11 | 500 | 5500 |
CC | c | 11 | 450 | 4950 |
DD | d | 10 | 950 | 9500 |
CC | e | 9 | 610 | 5490 |
BB | f | 8 | 310 | 2480 |
Eventually I'm trying to get the weighted total days of each region:
wieghtes total amount | average weighted factor | weighted total days | |
AA | 600 | 6500 | 10.83333333 |
BB | 310 | 2480 | 8 |
CC | 1060 | 10440 | 9.849056604 |
DD | 950 | 9500 | 10 |
Does anyone have anyidea how I can do this via DAX ?
Solved! Go to Solution.
Hi, @Anonymous
I created a sample and some measures then got the following results.
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.
Hi, @Anonymous
I created a sample and some measures then got the following results.
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.
@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.
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] )
)
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:
Date | Region | SubRegion | Total Amount | Holiday | Weekday |
2021/11/1 | AA | a | 50 | 0 | 1 |
2021/11/2 | AA | a | 5 | 0 | 2 |
2021/11/3 | AA | a | 90 | 0 | 5 |
2021/11/4 | AA | a | 60 | 0 | 4 |
2021/11/1 | AA | b | 40 | 0 | 1 |
2021/11/2 | AA | b | 60 | 0 | 2 |
.... |
So I did a measure based on subregion:
Calculating the working days, amount, and weighted factor by typing:
Year | Sum of total amount | WorkingDays | Weighted Facotrs |
2021 | |||
Nov | |||
AA | 305 | 21 | 4300 |
a | 205 | 10 | 1000 |
b | 100 | 11 | 3300 |
.... | |||
BB | 1150 | 40 | 15800 |
c | 500 | 16 | 8000 |
d | 400 | 12 | 4800 |
e | 250 | 12 | 3000 |
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |