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.
Hi everyone,
i`m currently stuck at the following problem and hopefully you can help me out with that:
So the requirement i need to implement is the following. For special combinations of several Product IDs i need a calculation of the sum of these combinations for each dealer. With this subtotal then i need to calculate the ratio of this combination to the total sum of all Dealercodes available.
Here`s an example:
| Dealercodes | 002 | 005 | 007 | 010 | 011 | 012 | 013 | 014 | 015 | 019 | 020 | 030 | 031 | 033 | 034 | 035 | 040 | 044 | 047 | 048 | 050 | 051 | 053 | 054 | 055 | |
SizeClass | 40<50 | 31 | 60 | 63 | 75 | 38 | 60 | 50 | 35 | 51 | 9 | 74 | 98 | 43 | 144 | 45 | 10 | 26 | 28 | 59 | 18 | 41 | 27 | 49 | 13 | 23 | 1170 |
SizeClass | 50<60 | 78 | 54 | 73 | 118 | 84 | 122 | 57 | 88 | 35 | 9 | 133 | 232 | 88 | 139 | 123 | 51 | 67 | 71 | 64 | 4 | 69 | 53 | 77 | 54 | 26 | 1969 |
Total | 109 | 114 | 136 | 193 | 122 | 182 | 107 | 123 | 86 | 18 | 207 | 330 | 131 | 283 | 168 | 61 | 93 | 99 | 123 | 22 | 110 | 80 | 126 | 67 | 49 | 3139 |
So the bold numbers are the totals for each dealer. After generating the sum for each dealercode of this combination, i take the sum and divide it with the total for both classes to generate the individual share of every single dealer.
F.Ex Dealercode 002: (109/3139)*100% = 3,47%
I tried several DAX - codings but they doesn`t seem to work as needed. I really appreciate your help.
Thanks and BR
Solved! Go to Solution.
Hi @atibu ,
Based on the information you provided, you can convert that table in the power query and then do the accounting for the percentages. The reference is as follows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY/BEUQhCEN78fwPAiJuLa6nX8P2vz4cL0wMSYhzlla/v1rt9VqeYrJHB3XbIxxuXO5I4BzdB0UDoGgYpPFuSASxdgZrRy0pxKsBSg6fWlnP3AdOl7wWaSMvMjrNg7eosiJijFvpZJ0wvRuxpNVu544pEmVTABLP78a9uHuv9Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dealercodes = _t, #"002" = _t, #"005" = _t, #"007" = _t, #"010" = _t, #"011" = _t, #"012" = _t, #"013" = _t, #"014" = _t, #"015" = _t, #"019" = _t, #"020" = _t, #"030" = _t, #"031" = _t, #"033" = _t, #"034" = _t, #"035" = _t, #"040" = _t, #"044" = _t, #"047" = _t, #"048" = _t, #"050" = _t, #"051" = _t, #"053" = _t, #"054" = _t, #"055" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Dealercodes", type text}, {"002", Int64.Type}, {"005", Int64.Type}, {"007", Int64.Type}, {"010", Int64.Type}, {"011", Int64.Type}, {"012", Int64.Type}, {"013", Int64.Type}, {"014", Int64.Type}, {"015", Int64.Type}, {"019", Int64.Type}, {"020", Int64.Type}, {"030", Int64.Type}, {"031", Int64.Type}, {"033", Int64.Type}, {"034", Int64.Type}, {"035", Int64.Type}, {"040", Int64.Type}, {"044", Int64.Type}, {"047", Int64.Type}, {"048", Int64.Type}, {"050", Int64.Type}, {"051", Int64.Type}, {"053", Int64.Type}, {"054", Int64.Type}, {"055", Int64.Type}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"40<50", Int64.Type}, {"50<60", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Dealercodes"}})
in
#"Renamed Columns"
Account_ = DIVIDE('Table'[sum_],SUM('Table'[sum_]),BLANK())
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks! this worked.
Hi @atibu ,
Based on the information you provided, you can convert that table in the power query and then do the accounting for the percentages. The reference is as follows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY/BEUQhCEN78fwPAiJuLa6nX8P2vz4cL0wMSYhzlla/v1rt9VqeYrJHB3XbIxxuXO5I4BzdB0UDoGgYpPFuSASxdgZrRy0pxKsBSg6fWlnP3AdOl7wWaSMvMjrNg7eosiJijFvpZJ0wvRuxpNVu544pEmVTABLP78a9uHuv9Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dealercodes = _t, #"002" = _t, #"005" = _t, #"007" = _t, #"010" = _t, #"011" = _t, #"012" = _t, #"013" = _t, #"014" = _t, #"015" = _t, #"019" = _t, #"020" = _t, #"030" = _t, #"031" = _t, #"033" = _t, #"034" = _t, #"035" = _t, #"040" = _t, #"044" = _t, #"047" = _t, #"048" = _t, #"050" = _t, #"051" = _t, #"053" = _t, #"054" = _t, #"055" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Dealercodes", type text}, {"002", Int64.Type}, {"005", Int64.Type}, {"007", Int64.Type}, {"010", Int64.Type}, {"011", Int64.Type}, {"012", Int64.Type}, {"013", Int64.Type}, {"014", Int64.Type}, {"015", Int64.Type}, {"019", Int64.Type}, {"020", Int64.Type}, {"030", Int64.Type}, {"031", Int64.Type}, {"033", Int64.Type}, {"034", Int64.Type}, {"035", Int64.Type}, {"040", Int64.Type}, {"044", Int64.Type}, {"047", Int64.Type}, {"048", Int64.Type}, {"050", Int64.Type}, {"051", Int64.Type}, {"053", Int64.Type}, {"054", Int64.Type}, {"055", Int64.Type}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"40<50", Int64.Type}, {"50<60", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Dealercodes"}})
in
#"Renamed Columns"
Account_ = DIVIDE('Table'[sum_],SUM('Table'[sum_]),BLANK())
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |