Forum Discussion
MrMilk
Helper I
3 years agoSales target does not appear
Hi all i have a sales fact table with salesman code and a sales target table with data, salesman code and value. In pbi if in a month the salesman had nothing in sales the sales target does not appe...
Mrxiang
Helper II
3 years agoWell, you can use the functions in DAX to implement this requirement. First, you need to create a calculation column that determines whether each salesperson made a sale in each month. Here's a sample code:
HasSales = IF(CALCULATE(SUM(SalesFactTable[Sales]), ALLEXCEPT(SalesFactTable, SalesFactTable[Salesman Code], Dates[Month])) > 0, 1, 0)
This column sums up each salesperson and month and determines if there was a sale. Returns 1 if there is a sale, 0 otherwise.
Next, you can use the SUMX function to calculate the sales goals for each salesperson. Here's a sample code:
SalesTarget = SUMX(FILTER(SalesTargetTable, SalesTargetTable[Salesman Code] = SalesFactTable[Salesman Code] && SalesTargetTable[Month] = Dates[Month] && SalesTargetTable[HasSales] = 1), SalesTargetTable[Value])
The formula picks out the sales targets that each salesperson has sold in each month and sums them up.
Finally, you can add this calculation column to your report to show the sales goals for each salesperson. Hope this helps you resolve the problem.