cancel
Showing results for
Did you mean:
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.

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

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

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

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

Proud to be a Super User!

Regular Visitor

That is the result shown in a matrix

Sales and Profit Margins are both measures.

Super User IV

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.

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

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

Proud to be a Super User!

Regular Visitor

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

Community Support

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

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

Thanks  I will look into this!

Announcements

#### Happy New Year from Power BI

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