Forum Discussion
Automatically redistribute the target per customer
- 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.
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_BI2 years ago
Helper IV
Thank you very much!
😄I am facing a slow calculation problem. It's taking a long time to calculate.