how to create formula in powerbi to auto follow each level rebate calculate

[oscarr29]

how to create formula in powerbi to auto follow each level rebate calculate

example
jack is VIP1, he buy 90unit iphone xs total 89,910.00
VIP1 rebate is 0.25%,if 89,910.00 multiplication 0.25%,he can get 224.78, But there is a limit for cashback which is 150 only VIP1 。

pls refer pic

Thanks

[v-danhe-msft]

Hi @oscarr29,

Based on my test, you could refer to below steps:

Sample data:

Tarnspose it with below code in advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2sUEhOLM5QSEpMzlbSUTI0NQCSRmDSGEwaGhgoxepEKxWlJiWWpAIFDPQMDIxMoQxjCyjDBCZiaKoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [level = _t, VIP1 = _t, VIP2 = _t, VIP3 = _t, normal = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"level", type text}, {"VIP1", type number}, {"VIP2", type number}, {"VIP3", type number}, {"normal", type number}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"level", type text}, {"Max cash back", Int64.Type}, {"rebate", type number}})
in
    #"Changed Type2"

And create below and measures:

if no max 2 = CALCULATE(SUM(Table1[buy]))*CALCULATE(MAX('Max cash back'[rebate]),FILTER('Max cash back',MAX('Table1'[Level])='Max cash back'[level]))

Save rebates = [if no max 2]-CALCULATE(SUM('Table1'[Max cash back]))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[oscarr29]

Hi @v-danhe-msft

it is formula can auto follow same level but different product Rebate?

like ken is VIP 3, buy (huawei p20 pro) and (iphone xs), (huawei p20 pro) Rebate 0.45% and (iphone xs) Rebate 0.30%.

Pls refer pic.

Thanks

[v-danhe-msft]

Hi @oscarr29,

Based on my test, I am afraid Power BI could not support this feature currenly, hope it could help you.

Regards,

Daniel He

[Vvelarde]

@oscarr29

Hi, can you upload an Excel file with bot sample tables?.  Will be easier instead of replicate it from 0

Regards

Victor