cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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?

Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors