cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
steveindazone Frequent Visitor
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:

 

sample data image.PNG

 

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:

 

example of Matrix to upload.PNG

 

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

Re: Formula Not Summing at Hierarchy

@steveindazone,

 

Try using SUMMARIZE Function instead.

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.
steveindazone Frequent Visitor
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])

 

Capture.PNG