Forum Discussion

schoden's avatar
schoden
Post Partisan
5 years ago
Solved

Dynamic Individual Target

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. 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    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.

6 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    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's avatar
      schoden
      Post Partisan
      5 years ago

      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's avatar
        AllisonKennedy
        Community Champion
        5 years ago

        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. 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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-Daily/ba-p/1463290

    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-Convert-to/ba-p/1476400

    • schoden's avatar
      schoden
      Post Partisan
      5 years ago

      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's avatar
        v-lionel-msft
        Community Support
        5 years ago

        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.