topic Re: Automatically redistribute the target per customer in DAX Commands and Tips

Automatically redistribute the target per customer

Rai_BI — Tue, 23 Jan 2024 14:45:06 GMT

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 this table there are the Date of Sale and Customer Code fields.
I also have a table with the goals for each client portfolio and in this table there are the fields Target Month, Target Value, Portfolio Code, Portfolio Name.

Consider the following situation:

The "AA" portfolio sold a total of 100,000, distributed among the 3 clients that are part of the portfolio. And this portfolio has a total target of 130,000

The "BB" portfolio sold a total of 300,000, distributed among the 5 clients that are part of the portfolio. And this portfolio has a total target of 350,000

The "CC" portfolio sold a total of 250,000, distributed among the 7 clients that are part of the portfolio. And this portfolio has a total target of 280,000

For the "AA" portfolio, I need to apply a target to the clients of this portfolio considering the percentage weight that each client represents in the total of 100,000 in the portfolio.

For the "BB" portfolio I need to apply a target to the clients of this portfolio considering the percentage weight that each client represents in the total of 300,000 in the portfolio.

For the "CC" portfolio I need to apply a target to the clients of this portfolio considering the percentage weight that each client represents in the total of 250,000 in the portfolio.

Is it possible to create a DAX measure that is capable of performing this goal calculation automatically?

Re: Automatically redistribute the target per customer

AUaero — Tue, 23 Jan 2024 19:11:22 GMT

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.

Re: Automatically redistribute the target per customer

Rai_BI — Wed, 24 Jan 2024 13:50:06 GMT

Thank you very much!
😄I am facing a slow calculation problem. It's taking a long time to calculate.

Re: Automatically redistribute the target per customer

AUaero — Wed, 24 Jan 2024 15:13:56 GMT

Rai_BI - I'm sorry to hear the calculation is slow. As I stated, I created a very simplified model to test out the DAX I provided. It is difficult to help troubleshoot slow calculations without actually seeing your data model.