topic Re: Sales target does not appear in DAX Commands and Tips

Sales target does not appear

MrMilk — Mon, 22 May 2023 21:27:29 GMT

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 🙃

Re: Sales target does not appear

vicky_ — Tue, 23 May 2023 05:44:23 GMT

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

Re: Sales target does not appear

MrMilk — Wed, 24 May 2023 08:26:10 GMT

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":

Re: Sales target does not appear

Mrxiang — Wed, 24 May 2023 08:39:25 GMT

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.

Re: Sales target does not appear

MrMilk — Wed, 24 May 2023 09:02:48 GMT

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

Re: Sales target does not appear

Mrxiang — Wed, 24 May 2023 09:08:01 GMT

Can you provide the PBIX file?

Re: Sales target does not appear

MrMilk — Wed, 24 May 2023 09:15:13 GMT

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