Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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"))
What's the logic of that formula?
Proud to be a Super User!
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
Amit - Refers to the specific excel cell
@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"))
@Subha , what are D18 and E18 here?
IF(D18>=50%,"T1",IF(D18+E18>=50%,"T2","T3"))
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |