Forum Discussion
Sum two columns based on two other column values
I have two numeric sales amounts in columns instead of rows (it would be signfiicant rework of the sql to change this :( ).
Column 1 is associated to inbound sales and column 2 is associated to outbound sales. Sales reps can be either inbound or outbound. The data looks like:
| Row | inbound$ | inboundRep | outbound$ | outboundRep |
| 1 | $ 100.00 | Joe | $ 115.00 | Sue |
| 2 | $ 211.00 | Joe | $ 211.00 | Joe |
| 3 | $ 75.00 | Sue | $ 100.00 | Joe |
i want to summarize and get one total for each sales rep.
PS., i will want to use this column in other calcs like YTD, MTD, ....
1 Reply
cmsouth wrote:I have two numeric sales amounts in columns instead of rows (it would be signfiicant rework of the sql to change this :( ).
I know you probably don't want to hear this, but the best approach is to do this rework and unpivot the data so that you have a single amount column, a single sales rep column and a new column for inbound/outbound.
The other way of doing this is to create a separate "sales rep" table then create 2 relationships back to this "sales" table. Because you can only have 1 active relationship between 2 tables you would have to pick one to be the active relationship, then you could write your measures as follows (assuming that the active relationship is to the inbound rep)
Inbound Amount = SUM('Sales'[inbound$] )
Outbound Amount = CALCULATE( SUM( 'Sales'[outbound$] ), USERELATIONSHIP( 'Sales'[outboundRep], 'Sales Rep'[Rep] ) )
Total Amount = [Inbound Amount] + [Outbound Amount]
