Forum Discussion

steveindazone's avatar
steveindazone
Frequent Visitor
8 years ago

Formula Not Summing at Hierarchy

Long time user and I've searched all around the web and the forums for an answer but I'm stumped. I'm having trouble getting a measure to sum up to the parent level in my matrix visual. So the measure is composed of a couple of different formulas. Here's what the sample data looks like:

 

 

I'm also using a date table. 

 

Running Total Current Quarter:

CALCULATE(SUM(Sheet1[Amount]),DATESQTD(DateTable[full_date]))

Final Total Q1 Prior Year: CALCULATE(SUM(Sheet1[Amount]),DATESBETWEEN(DateTable[full_date],DATE(YEAR(TODAY())-1,1,1),DATE(YEAR(TODAY())-1,3,31)))

Non Returning Dollars:

IF([Running Total Current Quarter]<=0,[Final Total Q1 Prior Year]*-1,BLANK())

 

So the Matrix has the dates for the quarter as columns across the top as shown below:

 

 

What I'm trying to do is get the totals in Non Returning Dollars to sum up to the Station level from the Account level. I understand that the measure technically is false at the Station level. I've tried solving this problem by using SUMX in a variety of ways, yet none of them accomplish the behavior I'm looking for. The formula I initially assumed would work would be:

 

SUMX(VALUES(Sheet1[Account]),[Non Returning Dollars])

 

In the picture I attached the Non Returning Dollars calc should ideally show -9973 at the subtotal level for station A since accounts EE and HH haven't billed for any money at that point in the quarter.

 

I've tried a ton of different formulas but I can't crack this. I'd appreciate any help and can elaborate on any further formulas I've tried or provide more sample data to work with.

2 Replies