data in two tables

[Anonymous]

Hi

I have two tables containing new sales and service disconnections

table 1)

date, sales man, sales amount in euro

table 2)

date, sales man, disconnection amount  euro

I want to get a graph that shows net amounts (total monthly sales - total monthly disconnections) per month per salesman

BR

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

You should add the month columns in your two tables, and UNION all the rows to one table, calculate the net amounts based on the new table. I try to reproduce your scenario and get expected results as follows.

First, add month calculated columns in the two table, mark all the disconnection amount negative using the formulas.

month = MONTH(Table7[Date])
month = MONTH(Table8[Date])
negative = 0-Table8[disconnection amount]

Second, union all the rows into one table using the following formula and get the new table shown in screenshot.

New = UNION(Table7,SELECTCOLUMNS(Table8,"Date",Table8[Date],"salesMan",Table8[sales man],"salesAmount",Table8[negative],"month",Table8[Month]))

Finally, create a measure used to calculate the net amount per month per salesman, and create a clustered chart. In the following screenshot, in each month, you will see different customer’s net amount. The data below the axis show the net amount is negative.

Net amount = CALCULATE(SUM(New[sales amount]),ALLEXCEPT(New,New[month],New[sales man]))

 

Best Regards,
Angelia

[Anonymous]

Angelia

super. thanks for that.