Confused what Dax formula to used

[AllanBerces]

Hi Good day,

Can anyone help me how to write the correct formula base from my data. from the squence 1 code E0 = 0%, 2 code E2 = 9% and so on. buy from 13 to 31 all code = 100%.

Seq	Code	Percent
1	E0	0%
2	E2	9%
3	M2	18%
4	MN	27%
5	E3	36%
6	E4	45%
7	E41	54%
8	E4B	63%
9	E4E	72%
10	E4F	81%
11	E4G	90%
12	E4X	100%
13	M10	100%
14	E5	100%
15	E51	100%
16	E5A	100%
17	E6	100%
18	I0	100%
19	M12	100%
20	I0A	100%
21	I1	100%
22	I1A	100%
23	I2	100%
24	I3	100%
25	I4	100%
26	I5	100%
27	I6	100%
28	I7	100%
29	X7	100%
30	X7A	100%
31	M9	100%

Thank you for the help

[v-xinruzhu-msft · Accepted solution]

Hi @AllanBerces 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc87DsMgEIThu1C7YBewQ5lITrSFU1tC3P8ayczQfSseP4yRLG3pzGluIznoZPnzEiv4JRs2FHIHK3mQRj/oF93pk7bM4a2BzfrRwGq9NbBreo6hfDaZ6aaIMd6eGpjfZeRjne68Sn/wzAWdcORDV7nTawH1WCcQD/3VEQ991tEOPcqRDqWd6UNG+pZLphUoKF89zfkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Seq = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Seq", Int64.Type}, {"Code", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let _calculate=([Seq]-1)*0.09
in if _calculate<=1 then _calculate else 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Percentage.Type}})
in
    #"Changed Type1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllanBerces]

Thank you very much

[smpa01]

@AllanBerces  you can do this

Measure 2 = 
var val = (MAX(t1[seq])-CALCULATE(MIN(t1[seq]),ALL(t1))) *0.09
return IF(val>1,1,val)