Forum Discussion
Dax help
Good day can anyone assist with the following dax measures for the following problem statement :
i have also attached a data sample, i would also like to create a calculated column to flag Preferred Customers from non preferred your assistance is highly appreciated
Direct Revenue: Revenue generated when a customer purchases products from Product Group A, B, or C. Such customers will be classified as Preferred Customers.
Indirect Revenue: Revenue generated from purchases of other product groups by Preferred Customers who also buy from Product Group A, B, or C.
|| || |Company Code|Fiscal Period|Product group|Ship-To Country|Customer|Base Unit of Measure|Quantity|NetSales| |5300|1|Product Group E|US|343663|EA|60.000 EA|48,384.00| |5300|1|Product Group E|US|10011981|EA|40.000 EA|2,000.00| |5300|1|Product Group E|US|10011981|#|0.000|(2,000.00)| |5300|1|Product Group E|US|10011981|EA|300.000 EA|7,500.00| |5300|1|Product group B|US|1590440|EA|1.000 EA|1,800.00| |5300|1|Product group B|US|1590380|EA|1.000 EA|3,500.00| |5300|1|Product group B|US|1590380|#|0.000|(3,500.00)| |5300|1|Product group B|US|923096|EA|1.000 EA|1,250.00| |5300|1|Product group B|US|923096|EA|1.000 EA|1,250.00| |5300|1|Product Group E|US|1716464|BIN|0.000 BIN|(333.50)| |5300|1|Product Group E|US|1716464|BIN|0.000 BIN|(280.60)| |5300|1|Product Group E|US|10002731|EA|48.000 EA|172.80| |5300|1|Product Group E|US|10002731|#|0.000|(172.80)| |5300|1|Product Group E|US|10002731|EA|48.000 EA|2,400.00| |5300|1|Product Group E|US|1582108|EA|0.000 EA|3,500.00| |5300|1|Product Group E|US|10012387|BIN|-680.000 BIN|(2,210.00)|
hi gomorasa ,
create a blank query. open the advanced editor. copy and paste the code below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZPLbsIwEEV/xUo3VHKj8SOOsyyIVl0UUVFWiEUEbhsJYpQ4C/6+kyBDQIg2aRdxri3rzJ2HF4tgZLe7NN+TkV2bgAZPWblKN2RqisyucT8t7LpaOfJZ2GqH+9lXtnt4t3i9yl2xx5NRVTq7NQXKYVoaMs8zR+wHeTVpWRU1861Kc5e5+vLEuFm6MSXKYEkXQSQAULNWpOc6EhnjyXyGi5BCKYFi/IiLghAASKOlpkLLsAH8EsYAGEs08zjZwnGKsi/tDr+Ghf+BJ933Nyag5Sym0U/OmvaQ4REXJSAleBo7sRjVPVhCX2OJXr4OrLOCedCtgl2QEi4gUVcT5FE3U39BXbYxZkoqWT+Fl4nPkBz0QAgRRp2G4haNawhVxxEDHovT7OtWpjEPdbfJ96yzRh44/2SKU9n1QUaaM9AeBp1G9cqL5ELHx+o/KH3eAIqxjkO7/AY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"NetSales", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "(Non/)Preferred Customer Flag", each if Text.Contains([Product group],"A") and [NetSales] >0 or Text.Contains([Product group],"B") and [NetSales] >0 or Text.Contains([Product group],"C") and [NetSales] >0 then "Preferred Customers" else "Non Preferred") in #"Added Custom"the flag is based on the logic below. if sales > 0 and prod groups contain A , B or C , then preferred else not preferred.
if this isn't the required logic, kindly elaborate and provide a sample output.
2 Replies
hi gomorasa ,
create a blank query. open the advanced editor. copy and paste the code below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZPLbsIwEEV/xUo3VHKj8SOOsyyIVl0UUVFWiEUEbhsJYpQ4C/6+kyBDQIg2aRdxri3rzJ2HF4tgZLe7NN+TkV2bgAZPWblKN2RqisyucT8t7LpaOfJZ2GqH+9lXtnt4t3i9yl2xx5NRVTq7NQXKYVoaMs8zR+wHeTVpWRU1861Kc5e5+vLEuFm6MSXKYEkXQSQAULNWpOc6EhnjyXyGi5BCKYFi/IiLghAASKOlpkLLsAH8EsYAGEs08zjZwnGKsi/tDr+Ghf+BJ933Nyag5Sym0U/OmvaQ4REXJSAleBo7sRjVPVhCX2OJXr4OrLOCedCtgl2QEi4gUVcT5FE3U39BXbYxZkoqWT+Fl4nPkBz0QAgRRp2G4haNawhVxxEDHovT7OtWpjEPdbfJ96yzRh44/2SKU9n1QUaaM9AeBp1G9cqL5ELHx+o/KH3eAIqxjkO7/AY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"NetSales", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "(Non/)Preferred Customer Flag", each if Text.Contains([Product group],"A") and [NetSales] >0 or Text.Contains([Product group],"B") and [NetSales] >0 or Text.Contains([Product group],"C") and [NetSales] >0 then "Preferred Customers" else "Non Preferred") in #"Added Custom"the flag is based on the logic below. if sales > 0 and prod groups contain A , B or C , then preferred else not preferred.
if this isn't the required logic, kindly elaborate and provide a sample output.
Thanks for the reply from adudani , please allow me to provide another insight:
Hi gomorasa ,Here are the steps you can follow：
1. Create calculated column.
Test = var _COUNTABC= CALCULATE( DISTINCTCOUNT([Product group]),FILTER(ALL('Table'),[Customer]=EARLIER([Customer])&&[Product group] in {"Product group A","Product group B","Product group C"})) var _COUNTOTHER= CALCULATE( DISTINCTCOUNT([Product group]),FILTER(ALL('Table'),[Customer]=EARLIER([Customer])&&NOT([Product group]) in {"Product group A","Product group B","Product group C"})) RETURN SWITCH( TRUE(), _COUNTABC>=1&&_COUNTOTHER=BLANK(),"Direct Revenue", _COUNTABC>=1&&_COUNTOTHER>=1,"Indirect Revenue")2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
