Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Revenue Total

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:

1.PNG

1 ACCEPTED SOLUTION

@Anonymous

 

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

 

Regards

 

Victor

 

 




Lima - Peru

View solution in original post

23 REPLIES 23
Matt_Alexander
Advocate III
Advocate III

Hi @Anonymous

 

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

 

Table Totals.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Not sure if that solves your issue?

 

Matt

Anonymous
Not applicable

@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.

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

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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.

@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
Not applicable

@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:

1.PNG
1.PNG
1.PNG
And for how my revenue column is modeled:

1.PNG

 

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

1.PNG

 

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

1.PNG

 

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
Not applicable

@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!

@Anonymous

 

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

 

Regards

 

Victor

 

 




Lima - Peru
Anonymous
Not applicable

@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!

@Anonymous

 

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

 

Regards

Victor

Lima - Peru




Lima - Peru

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 !

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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:

1.PNG

I then attempt to sum all revenue

1.PNG

Hi @Anonymous,

 

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.