Grand Total issues: sumx isn't the answer.

[martingaleh]

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]

[/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]

 

[akice]

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

 

[mole999]

, 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

 

[martingaleh]

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.

 

[akice]

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

 

[martingaleh]

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.

 

[akice]

Interesting. I use Excel 2016 (much preferred especially with the ability to use Variables) so perhaps a change was made to Max function for 2016(?). Anyhow, as long as you got it to work. Here is function reference:

https://msdn.microsoft.com/en-us/library/ee634228.aspx