cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 2
Community Support Team

## Re: Formula Not Summing at Hierarchy

https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Formula Not Summing at Hierarchy

@v-chuncz-msft

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])