Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
atibu
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

002005007010011012013014015019020030031033034035040044047048050051053054055 
SizeClass40<50316063753860503551974984314445102628591841274913231170
SizeClass50<607854731188412257883591332328813912351677164469537754261969
 Total109114136193122182107123861820733013128316861939912322110801266749

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
v-henryk-mstf
Community Support
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())

vhenrykmstf_0-1657873715740.png


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.

View solution in original post

2 REPLIES 2
atibu
Frequent Visitor

thanks! this worked. 

v-henryk-mstf
Community Support
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())

vhenrykmstf_0-1657873715740.png


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.