MEASURES

[Subha]

I am new to PBI and seeking assistance. I have a huge dataset but created below a table to mimic what I am looking for.

DATA TABLE
CUSTOMER	PO#	VALUE	CATEGORY
A	1	$    20.00	P3
A	2	$    40.00	P3
A	7	$  100.00	P1
A	10	$    50.00	P2
B	4	$  100.00	P1
B	3	$    50.00	P2
B	8	$    50.00	P2
D	5	$  200.00	P1
D	9	$  200.00	P1
D	6	$  100.00	P1

Category is a calculated column.  I created the below measures to create the second matrix below.

M1= COUNT('DATA'[PO#])

M2 = CALCULATE([M1],ALL('DATA'[CATEGORY])

M3 =DIVIDE([M1],[M2],0)

I am struggling to write  a measure to get me the results highlighted in red below. If excel I would have used the formula IF(D18>=50%,"T1",IF(D18+E18>=50%,"T2","T3"))

MATRIX VISUAL
CUSTOMER	P1(cellD17)	P2(CellE17)	P3(Cell F17)	CLASSIFICATION
A	25%	25%	50%	T2
B	33%	67%		T2
D	100%			T1

Any assistance would be greatly appreciated.

Thank you

[ryan_mayu]

@Subha 

What's the logic of that formula?

[Subha]

If the % of orders under category P1 is greater than 50% then classify as T1 if not add the percent of P1 and P2 and if that is greater than 50% classify as T2 else t3.  So the broad idea is to classify the orders by the $ value and then see what is teh % of orders by customer that fall under that range and then classify

[Subha]

Amit - Refers to the specific excel cell 

[amitchandak · Accepted solution]

@Subha , Based on what I got, a New measure like

measure =
var _m1 = calculate([M3], filter(all(Table[CATEGORY]), Table[CATEGORY] ="M1"))
var _m2 = calculate([M3], filter(all(Table[CATEGORY]), Table[CATEGORY] ="M2"))
var _m3 = calculate([M3], filter(all(Table[CATEGORY]), Table[CATEGORY] ="M3"))
return
IF(_m1>= .5,"T1",
IF(_m2+_m3>=.5,"T2","T3"))

[amitchandak]

@Subha , what are D18 and E18 here?

IF(D18>=50%,"T1",IF(D18+E18>=50%,"T2","T3"))