Calculate or Aggregate or other Function question

[propi_]

Hey there,

im new on the whole power, DAX and MDX stuff and trying now to figure a simple function out.
Is it possible to aggregate column c (revenue) by column b (customer), to get column d (SUM of Total Revenue in every Row)?

I tried it with calculate () and sumx() out but didnt't really worked...unfortunately there are no good DAX ord MDX Books (at least in german) at the moment...
I know I could do this by making an other Pivot Table to get Column D but then I had to copy all that Data and so on...
The reason I want to this is to cluster them later, so thats why I need the sum of revenues (Cluster by Total Revenues, e.g. Rev > 600 = big customer, if not its a small customer)

Hope you guys understand what I'm trying to do

Thanks so far!

Greets Andy

 

[skywriter]

Is this what you want?

Sheet1

*	A	B	C	D
Date	Customer	Revenue	*
tom
mike
john
mike
john
mark
rob
mike

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:88px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]June 11, 2014[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]June 11, 2014[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]June 11, 2014[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]June 11, 2014[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]June 12, 2014[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]600[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]June 13, 2014[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]June 14, 2014[/TD]

[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]June 15, 2014[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]400[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =SUMIFS($C$2:C2,$B$2:B2,B2) D3 =SUMIFS($C$2:C3,$B$2:B3,B3) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[scottsen]

Bruce, this is the Power Pivot forum, don't think you answer really helps him.

Propi, your english seems great. If you can write that well, I expect your reading is even better... so I would probably grab Rob Collie's power pivot book.

Define a measure (called calcuated field in 2013), that is just TotalRevenue := SUM(MyTable[Revenue])

Then simply drag your customer names onto rows, and TotalRevenue onto values... and you are dancing

 

[skywriter]

Bruce, this is the Power Pivot forum, don't think you answer really helps him.

My bad, I was searching zero replies opened it up and thought he wanted a plain old formula solution.

Now I know to look closer.

 

[propi_]

Hey thanks for the quick answers...

@ Bruce: Thanks, but won't help for 2 mio Rows

...so I would probably grab Rob Collie's power pivot book.

Only got the official from MS by Ferrari and Russo and found it hard to read...thanks for the tip...

Define a measure (called calcuated field in 2013), that is just TotalRevenue := SUM(MyTable[Revenue])

Then simply drag your customer names onto rows, and TotalRevenue onto values... and you are dancing

Hmmm, tried that but didn't worked out for me... i uploaded the file for you:

https://dl.dropboxusercontent.com/s...BrSTmsWgMi1qJ8174wjsQTb3Y0Q&expiry=1402562083

There is also the Final view it should look like, but maybe you got an other "more pro" way for me

It's just a simple banding/ cluster thing, but not based on a single row (like price). Its banded on a total value.
Sure i could do an aggregated SQL Statement with the Total Customer Revenue, but then my Cube would get smaller....(Sales Details etc.)

Thanks again!

Greets Andy

 

[Tianbas]

if you need it as a column in the datamodel you can use
=CALCULATE(sum(Table1[Revenue]),filter(Table1,Table1[Customer]=earlier(Table1[Customer])))

 

[propi_]

if you need it as a column in the datamodel you can use
=CALCULATE(sum(Table1[Revenue]),filter(Table1,Table1[Customer]=earlier(Table1[Customer])))

Yap, that's it! Thank you, you saved my day!

I knew i can do it with the calculate() function...now I'll try to find out what the earlier() thing does exactly

Thanks again!

Andy

 

[Tianbas]

Earlier is a strange name for a nice function. It gives you the result of the current row. In this case you calculate the sum of revenue column over all rows of the table that have the same customer as the current row

 

[scottsen]

In this case, I would go with a measure (calculated field). Well, actually, I would almost ALWAYS go with a measure when you can.

just TotalRevenue := SUM(Table1[Revenue])

is going to be fine. Put customer on Rows, TotalRevenue in values, and dance. No reason to store this in a calculated column that won't respond to slicers/filters/etc.

Propi, the italians book is hard for EVERYONE to read Rob's is WAY more approachable.

 

[XLBob]

here is another working formula and it will be faster than previous one when you have a large table. I really liked the Italians book, it will take you t next level once you grasp it. But for sure, Rob's book is also great. I have both and the new one as well

CALCULATE( SUM(TABLE1[REVENUE]),ALLEXCEPT(TABLE1,TABLE1[CUSTOMER]))