Reply
Frequent Visitor
Posts: 6
Registered: ‎02-09-2019

Weighted Average with calculated weights

[ Edited ]

Hello again PBI community, thank you for the responses to my last post. I will test and confirm answers on Monday Smiley Happy

 

I have quite a good problem to solve. I have 3 tables.

 

The first table contains IDs, start and end dates, and a value. 

 

The second and third tables are date tables, one linked to the start date, and one linked to the end date. (The purpose of this is so users can select any dates in the date slicer, not just the ones that happen to have data in the first table).

 

I have it set up so the user selects a range based on 2 slicers, the first range is a greater than date based on the end date, and the second is a less than date based on the start date. The purpose here is so the user can select any date range, and find the ID's that were 'live' in that range. Live means that if the selected dates are [end date>02/05/2018] and [start date<02/10/2018] all ID's that have overlap in this period (the whole period or part of the period) will show up.

 

The period created is 02/05/2018 to 02/10/2018. So any combination of being live for 1 day to all days in the selected period would show up.

 

Here is an example of the tables, and where the problem comes in. 

 

Table 1:

tableWeight.JPG

 

When the range of 02/05/2018 - 02/10/2018 is selected here is where I can get to:

tableWeight2.JPG

 

*Note: the days are inclusive so the entire period of being 'live' is 6 days.

 

The trouble is that the days in the period selected is a measure based on the slicer selections, the weights are also a measure, and I cannot divide the weights by the sum of the days to get the weighted amount. This is really where I need help. 

 

I included a category because there is a heirarchy of categories, so weighted averages within categories is what I need. This is also just an example. There are hundreds of categories, and tens of thousands of ID's, so no formulas with non-dynamic grouping are feasible.

 

If further explanations are needed then I will definately put some more color around the problem, just send post asking what I am missing!

Frequent Visitor
Posts: 6
Registered: ‎02-09-2019

Weighted average with calculated weights (Measures) and other intermediaries

[ Edited ]

Hello again PBI community, thank you for the responses to my last post. I will test and confirm answers on Monday Smiley Happy

 

I have quite a good problem to solve. I have 3 tables.

 

The first table contains IDs, start and end dates, and a value. 

 

The second and third tables are date tables, one linked to the start date, and one linked to the end date. (The purpose of this is so users can select any dates in the date slicer, not just the ones that happen to have data in the first table).

 

I have it set up so the user selects a range based on 2 slicers, the first range is a greater than date based on the end date, and the second is a less than date based on the start date. The purpose here is so the user can select any date range, and find the ID's that were 'live' in that range. Live means that if the selected dates are [end date>02/05/2018] and [start date<02/10/2018] all ID's that have overlap in this period (the whole period or part of the period) will show up.

 

The period created is 02/05/2018 to 02/10/2018. So any combination of being live for 1 day to all days in the selected period would show up.

 

Here is an example of the tables, and where the problem comes in. 

 

Table 1:

tableWeight.JPG

 

When the range of 02/05/2018 - 02/10/2018 is selected here is where I can get to:

tableWeight2.JPG

 

*Note: the days are inclusive so the entire period of being 'live' is 6 days.

 

The trouble is that the days in the period selected is a measure based on the slicer selections, the weights are also a measure, and I cannot divide the weights by the sum of the days to get the weighted amount. This is really where I need help. 

 

I included a category because there is a heirarchy of categories, so weighted averages within categories is what I need. This is also just an example. There are hundreds of categories, and tens of thousands of ID's, so no formulas with non-dynamic grouping are feasible.

 

If further explanations are needed then I will definately put some more color around the problem.

Super User
Posts: 10,605
Registered: ‎07-11-2015

Re: Weighted Average with calculated weights

So, in the example provided what should the Weighted Average numbers be? 50,000, 60,000 and 70,000 or something different and if different, what?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 6
Registered: ‎02-09-2019

Re: Weighted Average with calculated weights

@Greg_Deckler

 

The weighted average for category A should be the weights of each item [(6 * 50,000) + (5 * 60,000)] / total days in category (11) = 54,545. The weighted average is not filled in yet in my screen shots because I can't figure out the calculation. The blue fields filled in are what I am able to do so far. Having the days in period respond to the slicer selections is throwing me for a bit of a loop.

 

Thank you for the clarifying question, and any help from the community would be much appreciated Smiley Happy

Super User
Posts: 10,605
Registered: ‎07-11-2015

Re: Weighted Average with calculated weights

OK, got it. Can you post your current measure/column formulas?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Posts: 10,605
Registered: ‎07-11-2015

Re: Weighted Average with calculated weights

OK, here is what I came up with. See attached "Table15 2" and the 2 associated calendar tables. Page 11.

 

Here are the measures I created:

 

Days in Period = 
VAR __min = MAX('Table15_2_Calendar1'[Date])
VAR __max = MAX('Table15_2_Calendar2'[Date])
VAR __g1 = MAX([Start])
VAR __g2 = MAX([End])
VAR __table = GENERATESERIES(__g1,__g2,1)
RETURN
COUNTROWS(FILTER(__table,[Value]>=__min && [Value] <= __max))
Weights = [Days in Period] * MAX([Number])
Weighted Average = 
VAR __category = MAX([Category])
VAR __table = ADDCOLUMNS(ALL('Table15 2'),"__DaysInPeriod",[Days in Period],"__Weights",[Weights])
VAR __table1 = FILTER(__table,[Category] = __category)
VAR __days = SUMX(__table1,[__DaysInPeriod])
RETURN
SUMX(__table1,[__Weights])/__days

Still not sure about your 2/30/2018 date, that seems wrong to me...

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Attachment
Frequent Visitor
Posts: 6
Registered: ‎02-09-2019

Re: Weighted Average with calculated weights

@Greg_Deckler

 

Thank you for the suggestions Greg, and I apologize for the ambiguous data earlier. I tried to simplify the issue, and I think that was confusing. Here is a screen of the actual data. I tried your solution and it seems like it will work with a few tweaks. The problem I am having is that the GENERATESERIES function cannot be blank, and there will be more rows with no series generated than with series since the user will only be looking at short time frames at a time.

Snip for Greg.PNG

 

Here is how I tried to modify the DAX, but I can't quite figure it out even for step 1, the days in period measure.

Measure - Days in Period = 
VAR SlicerMat = CALCULATE(MIN('DateTable.1'[Date.1]), ALLSELECTED('DateTable.1'[Date.1]))
VAR SlicerSettle = CALCULATE(MAX('DateTable.2'[Date.2]), ALLSELECTED('DateTable.2'[Date.2]))
VAR Maturity = SUMX(MONEY_MARKETS,MAX(MONEY_MARKETS[Fin. Transaction.Fin. Transaction Level 01.Start of Term (Key)]))
VAR Settlement = SUMX(MONEY_MARKETS,MAX(MONEY_MARKETS[Fin. Transaction.Fin. Transaction Level 01.End of Term (Key)]))
VAR Table_Days = GENERATESERIES(Maturity, Settlement, 1)
Return
COUNTROWS(FILTER(Table_Days, [Value] >= SlicerMat && [Value] < SlicerSettle))

I think the missing piece is that it should only return something if it fits the criteria. Any ideas?

 

P.S. I love the 'Measure Totals, The Final Word' post. VERY helpful!

Highlighted
Super User
Posts: 10,605
Registered: ‎07-11-2015

Re: Weighted Average with calculated weights

Can you just do this?

 

Weighted Average = 
VAR __category = MAX([Category])
VAR __table = ADDCOLUMNS(ALL('Table15 2'),"__DaysInPeriod",[Days in Period],"__Weights",[Weights])
VAR __table1 = FILTER(__table,[Category] = __category)
VAR __days = SUMX(__table1,[__DaysInPeriod])
RETURN
IF(ISBLANK([Days in Period]),BLANK(),SUMX(__table1,[__Weights])/__days)

Attached updated with a row that is "out-of-bounds".


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Attachment
Frequent Visitor
Posts: 6
Registered: ‎02-09-2019

Re: Weighted Average with calculated weights

@Greg_Deckler

 

The trouble is in this measure

 

Days in Period = 
VAR __min = MAX('Table15_2_Calendar1'[Date])
VAR __max = MAX('Table15_2_Calendar2'[Date])
VAR __g1 = MAX([Start])
VAR __g2 = MAX([End])
VAR __table = GENERATESERIES(__g1,__g2,1)
RETURN
COUNTROWS(FILTER(__table,[Value]>=__min && [Value] <= __max))

 

In my actual data when I do the equivalent of 'VAR __table = GENERATESERIES(__g1,__g2,1)' there is a message that says Generate Series cannot be blank. Any suggestions?

Super User
Posts: 10,605
Registered: ‎07-11-2015

Re: Weighted Average with calculated weights

OK, this seems like it is getting a little hacky at this point, probably could use a refactoring but try this:

 

Days in Period = 
VAR __min = MAX('Table15_2_Calendar1'[Date])
VAR __max = MAX('Table15_2_Calendar2'[Date])
VAR __g1 = MAX([Start])
VAR __g2 = MAX([End])
VAR __g1a = IF(ISBLANK(__g1),DATE(1899,12,30),__g1)
VAR __g2a = IF(ISBLANK(__g2),DATE(1899,12,30),__g2)
VAR __table = GENERATESERIES(__g1a,__g2a,1)
RETURN
IF(ISBLANK(__g1a) || ISBLANK(__g2a),BLANK(),COUNTROWS(FILTER(__table,[Value]>=__min && [Value] <= __max)))

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!