Table 1 (LFSLTRNR) has monthly sales for an agent
Table 2 (Agents Target) has monhtly target for an agent.
Table 1 and 2 are connected via agent code
Report has Sale date filter from table1 and measure showing agent target for that slected month.
If there is no sale for an agent in a month then their target is coming as blank in the measure, but I want to show target even if there is no sale data in table 1 for an agent i.e. ignoring connection between two table when there is no sale.
Measure=
var yrs = SELECTEDVALUE(LFSLTRNR[STRNDT_YEAR])
var mon_start_date = CALCULATE(Min(LFSLTRNR[STRNDT_Month] ),ALLSELECTED( LFSLTRNR[STRNDT_Month] ))
var mon_end_date = CALCULATE(Max( LFSLTRNR[STRNDT_Month] ),ALLSELECTED(LFSLTRNR[STRNDT_Month] ))
var agent = SELECTEDVALUE('Team Details (Prop track)'[Sales Code])
return
return
CALCULATE(SUM('Agents Target'[Value]),
'Agents Target'[Sales Code]=agent,
'Agents Target'[Month]>=mon_start_date && 'Agents Target'[Month]<=mon_end_date && 'Agents Target'[Year]=yrs)
'Agents Target'[Sales Code]=agent,
'Agents Target'[Month]>=mon_start_date && 'Agents Target'[Month]<=mon_end_date && 'Agents Target'[Year]=yrs)
1 ACCEPTED SOLUTION
Thanks for the reply from @lbendlin , please allow me to provide another insight:
Hi @Raj12 ,
Do you want to display the data that table LFSLTRNR shows as null values in measure.
You can click to show items with no data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply from @lbendlin , please allow me to provide another insight:
Hi @Raj12 ,
Do you want to display the data that table LFSLTRNR shows as null values in measure.
You can click to show items with no data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, Thank you for your response. Measure is not working for the agent where there is no sale eg. for PAG:
This is the issue that I was facing where agent target is not showing when there is no sale in a month for an agent. Please help on this. Thanks
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
PFB data:
Agents Target Table
| Year | Month | Sales Code | Value |
| 2024 | 1 | Agent | 1515 |
| 2024 | 1 | Rax | 4545 |
| 2024 | 2 | Aga | 2323 |
| 2024 | 2 | Agent | 1316 |
| 2024 | 2 | Pag | 1212 |
| 2024 | 2 | Rax | 6767 |
| 2024 | 3 | Agent | 1751 |
LFSLTRNR
| Agent Code | Strndt | Strndt_Month | Strndt_Year |
| AGA | 13/02/2024 00:00 | 2 | 2024 |
| Agent | 11/01/2024 00:00 | 1 | 2024 |
| Agent | 13/02/2024 00:00 | 2 | 2024 |
| Agent | 14/03/2024 00:00 | 3 | 2024 |
| Rax | 11/01/2024 00:00 | 1 | 2024 |
