cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## How to create condtional subtotal calculation

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

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Frequent Visitor

thanks! this worked.

Community Support

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.

## Helpful resources

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors