Forum Discussion
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
- steveindazoneFrequent Visitor
I tried a couple of different variations of SUMMARIZE and they weren't returning any values. I tried the two formulas below:
Measure: SUMX(SUMMARIZE(Sheet1,Sheet1[Station],Sheet1[Account]),[Non Returning Dollars])
Measure 2: SUMX(SUMMARIZE(Sheet1,Sheet1[Station],Sheet1[Account],Sheet1[Date]),[Non Returning Dollars])