Forum Discussion

Rai_BI's avatar
Rai_BI
Icon for Helper IV rankHelper IV
2 years ago
Solved

Automatically redistribute the target per customer

In Power BI I have a table with customer portfolios. In this table there are the fields Customer Code, Customer Name, Wallet Code, Wallet Name. I also have the table with the sales record and in t...
  • AUaero's avatar
    2 years ago

    I've created a simplified model based on your description with the following measures that you should be able to modify to get the result you're looking for.

    Sales Measure:

     

    $ Sales = SUM(Sales[SaleAmount])

     


    Portfolio Target Measure:

     

    $ Portfolio Target = 
    CALCULATE(
        SUM(Goals[Target]),
        FILTER(
            Customers,
            COUNTROWS(VALUES(Customers[CustomerCode])) > 0
        )
    )

     

     

    Customer Target Measure:

     

    $ Customer Target = 
    VAR _CustomerSales = Sales[$ Sales]
    
    VAR _TotalSales = 
    CALCULATE(
        Sales[$ Sales],
        ALL(Customers),
        VALUES(Portfolio[PortfolioID])
    )
    
    VAR _PercentToTotal = 
    DIVIDE(_CustomerSales, _TotalSales)
    
    VAR _PortfolioTarget = Goals[$ Portfolio Target]
    
    VAR _CustomerTarget = 
    _PercentToTotal * _PortfolioTarget
    
    RETURN
    _CustomerTarget

     


    Result:

     



    Consider providing a dataset to work with on future requests; this will make it much easier to answer your questions.

    If this solves your problem, please mark this answer as the solution.