Sales target does not appear

[MrMilk]

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 appear. How to solve? 

p.s sorry for my english 🙃

[MrMilk]

https://www.dropbox.com/s/bbzbut06qx6ah03/TESTE%20analise%20comercial%20DM.pbix?dl=0

in the current month ( maio) sales code 21 as no sales and the Obj. € not appear

[MrMilk]

the formula should be

HasSales = IF(
CALCULATE(
SUM(SalesFactTable[Sales]),
ALLEXCEPT(SalesFactTable, SalesFactTable[Salesman Code], Dates[Month])
) > 0,
1,
0
)

but if i dont have sales the sales code does not even appear

[Mrxiang]

Can you provide the PBIX file?

[Mrxiang]

Well, 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.

[MrMilk]

Hi

thanks but i have a table:

and whith no sales ( i use the rule if is blank to put 0)  the Obj € ( sales target ) does not appear;

even the filter does not show the salesman code "21":

[vicky_]

Right click on your salesman dimension, and then select "Show items with no data"