Forum Discussion
data in two tables
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
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
2 Replies
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