Forum Discussion
Revenue Sum Card with OR filter from two tables
Hi,
Sorry if this is really basic. I'm new to Power BI and while I'm going through tutorials I am stuck with a real world problem that I've not learnt how to solve yet.
I have two tables
TABLE1
| ORDER NUMBER | REVENUE | STAGE |
| 1 | 100 | DISPATCHED |
| 2 | 50 | INSTALLED |
| 3 | 25 | INSTALLED |
| 4 | 7 | DISPATCHED |
TABLE 2
| ORDER NUMBER | FORECAST |
| 1 | 10/11/2018 |
| 2 | 20/11/2018 |
| 4 | 20/09/2018 |
Using Order Number as the primary key I have set up the relationship between the tables and can report on the orders with forecast date and stage. However not all orders have a forecast date.
What I want to do is create a Card with total Revenue for any orders that have a Forecast date of this month (in the example November) OR if the Stage is set to "Installed". So in the example above I'd expect the rows in green to be added up to give me $175. (Order Number - 1 2 and 3
This doesn't seem possible using visual filters. I'm assuming I need to setup a Measure but not sure exactly what I need to do.
Thanks for any assistance.
Hi strat,
You can create the below measure. Considering your table names are Table1 and Table2.TotalR = var curntmon=SUMX(FILTER(CALCULATETABLE(ADDCOLUMNS(Table1,"ForecastDate",LOOKUPVALUE(Table2[FORECAST],Table2[ORDER NUMBER],Table1[ORDER NUMBER]))),MONTH([ForecastDate])=MONTH(TODAY())),CALCULATE(VALUES(Table1[REVENUE]))) var installed= SUMX(FILTER(CALCULATETABLE(ADDCOLUMNS(Table1,"ForecastDate",LOOKUPVALUE(Table2[FORECAST],Table2[ORDER NUMBER],Table1[ORDER NUMBER]))),MONTH([ForecastDate])<>MONTH(TODAY())&& Table1[STAGE]="Installed"),CALCULATE(VALUES(Table1[REVENUE]))) return curntmon+installed
I have got the following result.
You can also download the pbix file here.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
2 Replies
- affan
Solution Sage
Hi strat,
You can create the below measure. Considering your table names are Table1 and Table2.TotalR = var curntmon=SUMX(FILTER(CALCULATETABLE(ADDCOLUMNS(Table1,"ForecastDate",LOOKUPVALUE(Table2[FORECAST],Table2[ORDER NUMBER],Table1[ORDER NUMBER]))),MONTH([ForecastDate])=MONTH(TODAY())),CALCULATE(VALUES(Table1[REVENUE]))) var installed= SUMX(FILTER(CALCULATETABLE(ADDCOLUMNS(Table1,"ForecastDate",LOOKUPVALUE(Table2[FORECAST],Table2[ORDER NUMBER],Table1[ORDER NUMBER]))),MONTH([ForecastDate])<>MONTH(TODAY())&& Table1[STAGE]="Installed"),CALCULATE(VALUES(Table1[REVENUE]))) return curntmon+installed
I have got the following result.
You can also download the pbix file here.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
