Hi Community,
I have two sales tables: Invoices and ERP Invoices. They are coming from different sources and there is a table called center which holds the data of the company centers which manage the sales and a table called dates which is the calendar table. There are multiple fields in all 3 tables but I give some examples to make it easier to get to the problem:
Invoices and Invoices ERP are almost the same:
| Date ID | Item | Invoice Amount | Center ID | ... |
| 237348 | Part3432 | 20000 | 2331 | |
| 237454 | Part2223 | 2500 | 2443 | |
the Center:
| Center ID | Center name | Product group | ... |
| 2331 | Kiev 233 | Sweets | |
| 2332 | Rome 11 | Bread | |
and the date:
| Date ID | Date | Year | Fiscal Month | ... |
| 237348 | 2023-08-15 | 2023 | 8 | |
| 237349 | 2023-08-16 | 2023 | 8 | |
The Sales tables, centers and Time are all having a defined relationship and work good.
The values coming from the different sales sources can sometimes be updated differently in the two tables. We have 2 calculated measures [Sales] and [ERP Sales] which calculates the amount of sales from the respeceted tables.
Now I want to create a new measure and calculate the sale amounts like this: if the [Sales] is blank in a whole fiscal month give me [ERP Sales] and if not, give the [Sales]. This should not consider the values of Product group or centers. What I mean is that the [Sales] for a special product group can be null in a month but other products can have value which matters. same goes for centers. so the result I want is like these two tables below:
| Fiscal Month | Sales | ERP Sales | New measure |
| 1 | 2000 | 1000 | 2000 |
| 2 | 2500 | 3000 | 2500 |
| 3 | | 3500 | 3500 |
| Total | 4500 | 7500 | 8000 |
| Fiscal Month | Product group | Sales | ERP Sales | New measure | |
| 1 | sweets | 750 | 750 | 750 | |
| 1 | breads | 1000 | | 1000 | |
| 1 | dairy | 250 | 250 | 250 | |
| 2 | sweets | 2000 | 1000 | 2000 | |
| 2 | breads | 500 | 1500 | 500 | |
| 2 | Batteries | | 500 | | |
| 3 | sweets | | 700 | 700 | |
| 3 | breads | | 1800 | 1800 | |
| 3 | dairy | | 1000 | 1000 | |
| Total | | 4500 | 7500 | 8000 | |
As shown in the first table the new measure is bringing in the Sales for Fiscal month 1 and 2 since there is a value there for it but for Fiscal month 3 it brings in the ERP Sales. in the second table it works the same based on month, and it does not consider the product group cases. (see that for batteries although there is no sales for it, it will not bring the ERP Sales because there are other values in the month 2 for Sales). I tried the below measures but it gives wrong total:
1)
Has Sales =
VAR SelectedMonth = SELECTEDVALUE(Date[Fiscal Month])
VAR SalesSum = CALCULATE([Invoices ], ALL('Center'[Product Group]),'Date'[Fiscal Month] = SelectedMonth)
RETURN SalesSum > 0
------
2)
Sales 2 = if([Has Sales],[Sales],[Sales ERP])
The total number is wrong and is showing the Sales ERP total. When I put the [Has Sales] measure into the table, it shows false for the Total row. What should I do?
