I am trying to set up ScoreCategory for every Salesman by Sales vs. average Sales.
Basic measures are correct.
For ScoreCategory I did these measures:
Average Production per ALL Sellers = CALCULATE([Sales YTD (2019)]/[Number of Sellers];ALL('ProductionTable'[Sellers_ID];'ProductionTable'[Sellers_Manager];'ProductionTable'[Region]))
and
Average Production per Seller = [Sales YTD (2019)]/[Number of Sellers]
and
Sales vs average = ([Average Production per Seller]/[Average Production per ALL Sellers])
Next step:
Scoring round up = ROUNDUP([Sales vs average];1)
While everything was fine.But when I tried to set up ScoringCategory like below, It shows me different, incorrect value in Table but in Total or in Card after filtering on one Seller it shows me right value of ScoringCategory.
ScoringCategory =
IF(OR([Scoring round up] = 0,0;[Scoring round up] = 0,1) ; 1;
IF(OR([Scoring round up] = 0,2;[Scoring round up] = 0,3) ; 2;
IF(OR([Scoring round up] = 0,4;[Scoring round up] = 0,5) ; 3;
IF(OR([Scoring round up] = 0,6;[Scoring round up] = 0,7) ; 4;
IF(OR([Scoring round up] = 0,8;[Scoring round up] = 0,9) ; 5;
IF(OR([Scoring round up] = 1;[Scoring round up] = 1,1) ; 6;7))))))
Please notice the different value for ScoringCategory calculated by "IF".
And when I filter on individual Seller
Differenc of ScoringCategory between Table and Card
Somewhere "IF" calculated right ScoringCategory, but somewhere it doesnt.
I Notice, that when ScoringCategory contain only 2 "IF functions" it calculated correct way, but When it contain more "IF function" like above, it is wrong.
Did anyone have a similar problem? Is there a different way for calculate ScoringCategory without "IF function"? I tried to use the interval with IF functions, but it didnt help...
I think it could work with a new interval table. Something like this below, but I'm not sufficiently experienced to be able to adjust it to my solution.
TEST= VAR RankingDimension = VALUES('ProductionTable'[Sellers_ID]) RETURN CALCULATE([Sales vs average];
FILTER(RankingDimension;
COUNTROWS(
FILTER('Support Scoring';
RANKX(ALL('ProductionTable'[Sellers_ID]);[Sales vs average];;DESC)>'Support Scoring'[Min]
&& RANKX(ALL('ProductionTable'[Sellers_ID]);[Sales vs average];;DESC)<= 'Support Scoring'[Max]))>0))
Thanks for your help :)
Aucun commentaire:
Enregistrer un commentaire