Forum Discussion

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

Handling Subtotals for Pre-Calculated (Non-Additive) Measures

I'd like to share a neat pattern that I've discovered recently.   Motivation I'm building a report where I'd like to use a small amount of summarized data from another report I've built that has a...
DAX Commands and Tips
tips & tricks
KN_HEALT47's avatar
KN_HEALT47
Frequent Visitor
5 years ago

I wrote this measure with the help of a forum user: 

YTD Budget =
IF(OR(ISINSCOPE(Expenses[partner_name]),ISINSCOPE(Expenses[VendorJA])), BLANK(),CALCULATE(SUM(Budget[Amount]), MONTH(Budget[Merged Month Year]) <= MONTH(TODAY())-1))
 
and the result is this:
 

 

 

I like that the invoice detail is blank for the parts of the budget that do not have this detail the problem is that the YTD budget is aggregating on repeat for each account line ( see 50001 Payroll). Idealy the total budget YTD would populate for 50001 payroll only and so on for each account and 493,604.01 would only be the total at the bottom. Both budget and Actuals share the account detail, only budget does not go further down into invoice and vendor name. Based on your post above I think it could help with this issue but not sure how to execute given limited knowledge on difference in query, measure, and use of variables. Any suggestions? thanks in advance.

 
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

This seems like a different problem, like, maybe your Budget table doesn't have a working relationship with the Expenses table.