Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
Hi, can you upload an Excel file with bot sample tables?. Will be easier instead of replicate it from 0
Regards
Victor
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |