DAX Mobile Brand wise rank

[Anonymous]

Hi every one,

I have mobile brands with excel file.  Please find excel file in this link.

should count "column 0" and multiply with 7

should count "column 1" and multiply with 6

remaing also decrease the number and multiply with counts.

need to do addition for all columns multiply with respective number. then provide rank each brand wise.

Please refer my excel file to more understad.

my output should come like below table....

mobile	Score	Rank
Apple	6966	1
Spice	6819	2
Samsung	6643	3
Motorola	6627	4
Xiaomi	6627	5
OnePlus	6619	6
Karbonn	6597	7
Oppo	6594	8
LYF	6580	9
Lava	6578	10
Nokia	6549	11
Honor	6461	12
Huawei	6436	13
Asus	6425	14
Gionee	6396	15
Acer	6364	16
HTC	6363	17
Vivo	6359	18
Xolo	6264	19
Intex	6250	20
Lenovo	6247	21
Micromax	6154	22
Jio	6064	23

can any one help on this....

regards

Venu

[hnguy71]

I don't understand your logic with the count and multiplication but I'll assume you want something similar:

Countx7 = COUNT('Mobile Score'[Mobile 0])*7 + COUNT('Mobile Score'[Mobile1])*6

After you get your count that you want the you should add a measure to rank them:

RankMe = RANKX(ALLSELECTED('Mobile Score'[Mobile 0]), [Countx7])

[Anonymous]

@hnguy71 Thanks for your response....

should apply logic for all columns....

should count "column 0" and multiply with 7

should count "column 1" and multiply with 6

should count "column 2" and multiply with 5

should count "column 3" and multiply with 4

should count "column 4" and multiply with 3

should count "column 5" and multiply with 2

should count "column 6" and multiply with 1

when you count on each column it showing ranking on different brands. when you multiply score to each column. 

then it showing different ranking for each brand.

should do addition after apply multiplication, and provide ranking.

[hnguy71]

I'm guessing you just add more counts to all the columns.

Countx7 = 
COUNT('Mobile Score'[Mobile 0])*7 + 
COUNT('Mobile Score'[Mobile1])*6 + 
COUNT('Mobile Score'[Mobile2])*5 + 
COUNT('Mobile Score'[Mobile3])*4 + 
COUNT('Mobile Score'[Mobile4])*3 + 
COUNT('Mobile Score'[Mobile5])*2 + 
COUNT('Mobile Score'[Mobile6])*1

[Anonymous]

@hnguy71 thanks for your valueble time.....

your formula gives results like below, total value is wrong, and ranking brand also wrong. 

my expected output have already mention in above.... can you check with my excel file. before provide solution.

[hnguy71 · Accepted solution]

The logic I built for you was rather correct. The numbers were coming out wrong because you didn't have a master list of mobile values. The rank measure stays the same.

EDIT: Added tiebreaker

Step 1: Create a new table:

MobileValues = VALUES('Mobile Score'[Mobile 0])

Step2: Create your three measures:

Countx7 = 
var Mobile0 = CALCULATE(COUNT('Mobile Score'[Mobile 0]), TREATAS(VALUES(MobileValues[Mobile 0]), 'Mobile Score'[Mobile 0])) * 7
var Mobile1 = CALCULATE(COUNT('Mobile Score'[Mobile1]), TREATAS(VALUES(MobileValues[Mobile 0]), 'Mobile Score'[Mobile1])) * 6
var Mobile2 = CALCULATE(COUNT('Mobile Score'[Mobile2]), TREATAS(VALUES(MobileValues[Mobile 0]), 'Mobile Score'[Mobile2])) * 5
var Mobile3 = CALCULATE(COUNT('Mobile Score'[Mobile3]), TREATAS(VALUES(MobileValues[Mobile 0]), 'Mobile Score'[Mobile3])) * 4
var Mobile4 = CALCULATE(COUNT('Mobile Score'[Mobile4]), TREATAS(VALUES(MobileValues[Mobile 0]), 'Mobile Score'[Mobile4])) * 3
var Mobile5 = CALCULATE(COUNT('Mobile Score'[Mobile5]), TREATAS(VALUES(MobileValues[Mobile 0]), 'Mobile Score'[Mobile5])) * 2
var Mobile6 = CALCULATE(COUNT('Mobile Score'[Mobile6]), TREATAS(VALUES(MobileValues[Mobile 0]), 'Mobile Score'[Mobile6]))
return
Mobile0+Mobile1+Mobile2+Mobile3+Mobile4+Mobile5+Mobile6

TieBreaker = CALCULATE(COUNT('Mobile Score'[Mobile 0]), TREATAS(VALUES(MobileValues[Mobile 0]), 'Mobile Score'[Mobile 0]))

RankMe = IF(HASONEVALUE(MobileValues[Mobile 0]), RANKX(ALLSELECTED(MobileValues[Mobile 0]), [Countx7] + [TieBreaker],,DESC,Dense), BLANK())

Step3: With your new master list inside a table:

[Anonymous]

@hnguy71   Thanks for your reply.... really done good job...