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
AdamIzzo
Regular Visitor

Sum of row level data instead of column in table

Good day,

 

I am trying to get the total of my row level data and not the total on the column (See example below)

 

                                Store 1         Store 2        Store 3             Total

Sales                        10000            12000          7000              29000

Profit Margin %         10%               15%           18%                18%

Profit                         1000               1800          1260               5220

 

I want to be able to work out a way to get my profit total to be 1000+1800+1260 = 4060.

 

I am not sure what to do about the total profit margin % as that is irrellevant for the total figure I just need total profit.

I am currently using MAX(Profit margin %) in order to bring it into a measure

 

I am pulling in sales figures from multiple tables in a measure and the profit margin from multiple tables in a measure.

 

Any assistance would be greatly appreciated.

 

Thanks in advance!

 

 

7 REPLIES 7
jthomson
Solution Sage
Solution Sage

Can you not transpose your source data once it's all loaded up into Power Query, or use the features that the matrix visual has?

Greg_Deckler
Super User
Super User

So, is that source data or the result shown in a Table or Matrix visualization?


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

That is the result shown in a matrix

 

Sales and Profit Margins are both measures.

Did you turn on Column subtotals? I know it soundsl like the wrong thing but it should give you a column of totals on the right. It's in the format area under Subtotals.


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

I have put in the totals but it does not calculate the total properly.

 

I am not sure how to bring the profit margin percentage into a measure without using a MAX or MIN.

 

For example Profit measure = Sales x MAX(ProfitMargin)

 

The total then works out total sales times the max profit margin which is incorrect

hi, @AdamIzzo

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

 

IF not your case,Please share your sample pbix  for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks  I will look into this! 

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.