Grand Total issues: sumx isn't the answer.

martingaleh

Board Regular
Joined
Jul 18, 2011
Messages
83
I don't think dax can handle aggregated functions, but lets try. It stumped the powerbi community
[FONT=&quot]I've found dax is not super great at aggregate functions because you basically have to rebuild it for grand total purposes (and referencing them). Here's an example on grand totals I can't get right.[/FONT]
[FONT=&quot]
large
[/FONT]

[FONT=&quot]I don't know why there's a pencil in the way. Maybe there won't be after I'm done with this post. Just in case, that last column is relu, and it's basically max([in]+[cr],0), except there are no obvious max functions in dax so I used if statements rather than look up an esoteric one. The grand total should be 09, but dax just evaluates the grand total as if a and b aren't filtered, rather than adding the results of a and b evaluated seperately. How do I fix this? I have a sample spreadsheet, where would I upload it? Oh, I know. Google![/FONT]
[FONT=&quot]https://drive.google.com/file/d/1KM7uJBz2i8HKlH8UCRSMBUcoQ0KJ5tnM/view?usp=sharing[/FONT]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I don't think dax can handle aggregated functions

Interesting comment as Dax entire existence revolves around aggregating values...

And surprised you say it stumped the powerbi community as this is a basic question...solved using Dax MAX function...and SUMX.

...where would I upload it? Oh, I know. Google!
Now that is funny.

Here is my reply file:

Reply Download
 
Last edited:
Upvote 0
, where would I upload it?
Google has a slight capacity advantage that could never be replicated a forum. Some options exist for display of specifics, but attachments won't be supported
 
Upvote 0
My apologies, it seems I made the sample a little too simple. Here it is with a lot more lines. Your sumx function works by evaluating max on individual lines of the table, I think. This won't do because we have to evaluate aggregated lines from the table. Here's a better example. Maybe i"m misunderstanding your equation, but when I make the data bigger, it breaks.
https://drive.google.com/open?id=1yNHR0Qq3F1Tbc0zCgGY1d_2gqQnwHc7R

The issue with dax is it aggregates values, but after they're aggregated, it can't easily make calculations against them. For example, sql solves it by nesting sql. Most report writers solve it by allowing you to address cells positionally. Not sure what tableau does, I've always wanted to look, but never had the time.
 
Upvote 0
Code:
total Amount:=SUM( Table1[amt] )
in:=CALCULATE( [total Amount], Table1[t] = "i" )
cr:=CALCULATE( [total Amount], Table1[t] = "c" )
relu:=SUMX( VALUES( Table1[c] ) , MAX( [in] +  [cr], 0 ) )

Since in a measure, my 'relu' code iterates over each unique value in the Table1[c] column, aggregates all values for both 'i' and 'c', adds these aggregated values 'i' + 'c', and MAX returns the greater of addition or 0. SUMX then adds up these values to get grand total.

A calculated column works row by row of a table; measures work on aggregated values based on filter context.

So all I did was add my 'relu' measure to your new table and got the results you are wanting per my understanding. What am I not getting?

Reply File2
 
Upvote 0
I'm not exactly sure what the issue is but your equation didn't work:
https://imgur.com/a/bVqp6

I'm using excel 2013. If it worked for me, it would be exactly the answer I"d be looking for. I tried it with an if statement instead and lo and behold it worked. I'm not sure why, but now that I know the answer it will be easier to figure it out. Too bad this doesn't give you the opportunity to mark the answer that solved the problem.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top