Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Angelia
super. thanks for that.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |