How to create condtional subtotal calculation

[atibu]

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

[atibu]

thanks! this worked. 

[v-henryk-mstf · Accepted 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.