Revenue Total

[Anonymous]

A very easy question I thought I'd never have to ask... but at the bottom of this table, how can I display the total of all revenue? When I change the field from 'Don't Summarize' to 'Sum', it gives the total on each and every line. Not what I'm looking for:

[Matt_Alexander]

Hi @Anonymous

With the table selected there should be an option to include the total that you should be able to activate:

Not sure if that solves your issue?

Matt

[Anonymous]

@Matt_Alexander Well I would like to have the grand total at the bottom of my table so I need Total turned on. I just don't know why it's changing the individual amount for each row. I want the individual amounts for each company to remain what they are, just with the grand total of all revenue at the bottom.

[Matt_Alexander]

OK, and with the measure / aggretion set to 'Don't Summarize' and the Totals turned on in the table format, it doesn't do it correctly?

As Greg says, very odd! Apologies I can't be of more help, works fine for me in various different scenarios. 

Matt

[Greg_Deckler]

@Matt_Alexander - I think I caught something in his reply that he might be trying to do the sum in Power Query? If that is the case, yeah, he is going to get some weird things happening.

[Anonymous]

@Greg_Deckler @Matt_Alexander I don't know if it helps or even matters ( I don't think it should) but the Revenue column isn't in the same dataset as everything else. I have 2 objects that are connected through a unique ID. So the dataset I'm using for the Revenue consists of nothing but a unique ID and the revenue amount associated with that ID. I then created a relationship between the two tables based on the Unique ID's. I'm going to try to delete the relationship and just merge the queries instead on that ID to see if it behaves any differently.

[Matt_Alexander]

@Greg_DecklerCould well be - I've just tried aggregating in Power Query but still working ok, I can't seem to reproduce the error. One thing I've noticed though is that I never have the Don't Summarize option active - doesn't that only appear for text entries?

Perhaps there's a format issue something in the model...

[Anonymous]

@Matt_Alexander @Greg_Deckler Here are screenshots step by step of what I did. Maybe one of you can see where I'm going wrong:




And for how my revenue column is modeled:

Which gives me this when using 'Don't Summarize'

The moment I change it to 'Sum' on that column

[Matt_Alexander]

None the wiser I'm afraid, I have just recreated a similar setup and all works fine. Perhaps if you can share the file we can help quicker.

I didn't need to aggregate in Power Query - when I drag the measure / column to the table it automatically aggregates it by company or the attribute that I've selected

[Anonymous]

@Matt_Alexander I wish I could but I cannot hand over company credentials since I'm logged into our CRM system through these datasets. I will keep playing around with it to figure out what's going on. Thank you and @Greg_Deckler for you help!

[Vvelarde · Accepted solution]

@Anonymous

Hi, try changing in the relationship Cross Filter Direction to BOTH

Regards

Victor

[Anonymous]

@Vvelarde You. Are. A. Genius. That was plaguing me for days and that's all it was! If you don't mind me asking, what exactly does this setting do? I never know when and how to use it. Thank you so much again!

[Vvelarde]

@Anonymous

My english is not the best, so to avoid any confusion i prefer that @Greg_Deckler  explain the correct behavior of this action.

Regards

Victor

Lima - Peru

[Greg_Deckler]

Nice one @Vvelarde - mystery solved!!

@Anonymous - So basically the cross-filter direction of relationships means that selecting items in one table filters out items in the other table in the direction of the filter. So, let's do a simple example:

Table A

ID, Revenue

A,  100

B,  200

Table B

ID, Products Sold

A,  4

B,  2

So, if your filter direction is A->B

And you place "ID" from Table A into a table visual and SUM of Product Sold from Table B, everything will work correctly. The row context of the table visual will filter Table B for the A row to the A row in Table B and same for row B. Everyone is happy.

But, if you place "ID" from Table B and SUM of Revenue from Table A, not so happy. You will get 300 for both rows. This is because nothing is filtering from B to A and thus there is, in effect, no relationship between the two tables from that perspective.

By switching the filter direction to both A<->B then everything works the way you would think they would in both cases above.

Note, cross filter direction is important to know about and more complex models will sometimes require one or the other, there is no 100% this is always how it has to be. So, generally uni-directional relationships work just fine but in more complex models, sometimes you have to switch it to "Both".

Let me know if that explains things sufficiently.

[Anonymous]

@Greg_Deckler Ok I see now. Makes sense! Thank you for the explanation. Thank all of you for hepling with my issue, it's really appreciated @Vvelarde @Matt_Alexander @danextian !

[Greg_Deckler]

OK, so the firs table does not represent your raw source data.

[Anonymous]

@Greg_Deckler Does that mean I'll be unable to do what I'm attempting to do?

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[Anonymous]

@Greg_Deckler This is nice but seems a little complicated for what I'm trying to achieve? I just need my rows to stay put while the grand total of all revenue is at the bottom. There are no measures being calculated:



I then attempt to sum all revenue

[danextian]

Hi @Anonymous,

Did you create a calculated column from the sum of revenue? 

[Anonymous]

@danextian I believe so. In the query editor there were multiple revenue amount for each account so I grouped everything by the company id and summed by the revenue column. I didn't need each individual payment amount for each company, but just what the totals were for each account.