Dynamic Individual Target

[schoden]

Hi Community, 

Please help or share any resources related to the case.

I have  sales table on monthly basis( employe id, sales id, emp name, sale Date, sales amount ) 

I want to create a table that stores individual target , granularity at month level ( call it individual table)

The purpose is compare montly sales by individual to its montly target.  

How can I automate it  when individual gets promoted so does montly target change.

How can I link individual table  to the sales table, showing the correct individual target.

Any slightest idea will be highly appreciated.

Thanks. 

[amitchandak]

@schoden , Not very clear. But I have two blogs on how to convert yearly target to daily and with some changes that can be used for months 

powerbi Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

PowerBI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[schoden]

@amitchandak  

I have sales table that comes from SQL. Sales table has info on employee name, employee id, sales date and sales amount. 

Now I want to compare individual sales with a target value which I created using dax on Monthly basis( it is static )  

To this point, it works perfectly fine. 

However I want to make it more dynamic, meaning the target value changes when the individual gets promoted.

I have no Table in SQL datasource that contains individual target value. 

How can I achieve this, compare individual target with sales amount that automatically changes with individual promotion?🙂

Cheers. 

[v-lionel-msft · Accepted solution]

Hi @schoden ,

you can create such a calculated table.

Please refer to my .pbix file.

The target sales amount always changes dynamically with the latest actual sales.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@schoden  If I'm understanding correctly, you're looking to create/store data? Power BI is designed for reporting on existing data, not so great at collecting/storing information. Where do your sales figures come from? You can use Power Automate to automate target change with promotion of sales person (or based on previous month sales figures), and lots of choices for where to store that information: Excel, SharePoint List, CDS, Azure, etc. 

Does that help at all? Or just confuse matters?

[schoden]

@AllisonKennedy  yes Alison you got me 100% . I am trying to create/store data in power BI. 

Currently I have created a dax  (values are static )of Individual target that compares with sales amount. 

I wanted to make individual target dynamic that changes with individual promotion date. 

Thanks ,  I will research more on it.

[AllisonKennedy]

Good luck @schoden 

You will need to choose another application to store the data (Excel is easy). Power BI can only perform calculations on data that already exists in the data model.