Forum Discussion
Helper IVAutomatically 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 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?
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.
3 Replies
- AUaero
Responsive Resident
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.
Rai_BIThank you very much!
😄I am facing a slow calculation problem. It's taking a long time to calculate.