Summarize with filter from a related table

DAX DAX Calculations
1

I have a measure that works using summarize. Now, I need to create the same measure based on fiscal week to date. I can create a WTD measure no problem, but not sure how to combine with Summarize.

Can someone please point me in the right direction?

Summarize Measure:
Average Product Line Budget By ProductLine USD =
SUMX(
SUMMARIZE(
SalesAggDept,
SalesAggDept[DivisionHierarchyNodeDescription] ,
SalesAggDept[ProductLineHierarchyNodeDescription] ,
SalesAggDept[ProductLineBudgetUSD],
“@Totals” ,
[Average Product Line Budget USD] ),
[@Totals]
)

Example Fiscal WTD Measure:
Budget USD Fiscal WTD =
var _selectedFiscalWeek= SELECTEDVALUE(Dates[FiscalWeekNumber],0)
var _selectedFiscalYear= SELECTEDVALUE(Dates[FiscalYearOnly],0)
var _dayofWeek= SELECTEDVALUE(Dates[DayOfWeekNumber],0)
Return
CALCULATE([Budget USD],
Filter(All(Dates),Dates[FiscalWeekNumber]= _selectedFiscalWeek && Dates[FiscalYearOnly]= _selectedFiscalYear && Dates[DayOfWeekNumber] <=_dayofWeek)
)

2

Hi @DeanJ, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. Please take note of the following:

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Include the masked demo pbix file.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Kindly check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

3

Sorry for the vague wording. I figured out how to do what I needed.

Solution Measure=
var _selectedFiscalWeek= SELECTEDVALUE(Dates[FiscalWeekNumber],0)
var _selectedFiscalYear= SELECTEDVALUE(Dates[FiscalYearOnly],0)
var _dayofWeek= SELECTEDVALUE(Dates[DayOfWeekNumber],0)
Return
SUMX(
CALCULATETABLE(
SUMMARIZE(
SalesAggDept,
SalesAggDept[DivisionHierarchyNodeDescription] ,
SalesAggDept[ProductLineHierarchyNodeDescription] ,
SalesAggDept[ProductLineBudgetUSD],
“@Totals” ,
[Average Product Line Budget USD] ),Filter(All(Dates),Dates[FiscalWeekNumber]= _selectedFiscalWeek && Dates[FiscalYearOnly]= _selectedFiscalYear && Dates[DayOfWeekNumber] <=_dayofWeek)),
[@Totals]
)