Forum Discussion

pschn1993's avatar
pschn1993
Frequent Visitor
4 years ago
Solved

Subtotals are the same as the total values

I am using the below formula and the subtotals are the same as the total for YTD Actl & YTD Bdgt. How can I adjust my formula to display the correct subtotals?

 

YTD Bdgt = var selectvalue = SELECTEDVALUE('Calendar'[monthnum])
var t =
CALCULATE(
SUM(financials_bdgt[Budget accounting amount]),
FILTER(
ALLEXCEPT(financials_bdgt,'Calendar'[fy],Mills[Location],Dpt[deptabrv],Accounts[Dim Ledger Accounts.Main account]),
financials_bdgt[Period - Fiscal.Fiscal Month] <= selectvalue
)
)
return
IF(
MINX(financials_bdgt,financials_bdgt[Period - Fiscal.Fiscal Month]) = selectvalue,
t,
BLANK()
)
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI pschn1993,

    I'd like to suggest you take a look at the following link to know how to use if statement with Dax functions to check the current row context level and redirect to corresponding context level expressions:

    Clever Hierarchy Handling in DAX - SQLBI

    In addition, if you are working with multiple aggregation scenarios(they may appear when the measure formula is calculated on the total level), you can also take a look at blow blog about 'measure total level calculation' with multiple aggregations.

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI pschn1993,

    I'd like to suggest you take a look at the following link to know how to use if statement with Dax functions to check the current row context level and redirect to corresponding context level expressions:

    Clever Hierarchy Handling in DAX - SQLBI

    In addition, if you are working with multiple aggregation scenarios(they may appear when the measure formula is calculated on the total level), you can also take a look at blow blog about 'measure total level calculation' with multiple aggregations.

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng