calculated % of total based on one row, apply % to all other rows

[parry2k]

hi there,

Just interesting question here. So I have a transaction table with product , category, amount and another table called category and both table are related on category.

IN transaction table, not all the products have category, only one product id has categor record. What I'm looking for is to sum all the maount of this one product and calculate share by category. and then apply that share of each category to other products in transaciton table.

In following example A1 the main product with categor and share is calculate for each category from total amount of product A1

and same % is now applied to rest of the products to show amount by category (right hand side pivot table)

See image below (i put this as a sample)

[v-jiascu-msft]

Hi @parry2k,

Could you please mark the proper answer as solution or share the answer if it's convenient for you? That will be a help to the others.

Best Regards!
Dale

[parry2k]

Hi Dale,

Thanks for the follow up. I don't think I received the solution yet and hoping someone will help me here.

P

[v-jiascu-msft]

Hi @parry2k,

If you want to try @danextian 's method, you only need to do one step more, which is adding a calculated column. It works.

NewAmount =
CALCULATE ( SUM ( 'Final'[Amount] ), ALLEXCEPT ( 'Final', 'Final'[Product] ) )
    * [Share]

Or, you can try this method.

1. Add an index in Query Editor. (must start from 0).

2. Add a new calculated column.

NewCategory =
VAR currentIndex = 'Transaction'[Index]
RETURN
    CALCULATE (
        VALUES ( 'Transaction'[Category] ),
        FILTER (
            'Transaction',
            'Transaction'[Product] = "A1"
                && 'Transaction'[Index] = MOD ( currentIndex, 5 )
        )
    )

3. Create a measure.

Values =
VAR ratio =
    DIVIDE (
        CALCULATE ( SUM ( 'Transaction'[Amount] ), 'Transaction'[Product] = "A1" ),
        CALCULATE (
            SUM ( 'Transaction'[Amount] ),
            FILTER ( ALL ( 'Transaction' ), 'Transaction'[Product] = "A1" )
        ),
        0
    )
RETURN
    CALCULATE (
        SUM ( 'Transaction'[Amount] ),
        ALLEXCEPT ( 'Transaction', 'Transaction'[Product] )
    )
        * ratio

4. Create a visual Matrix.

If it helped, please mark the proper answer as solution.

Best Regards!

Dale

[danextian]

By the way, here are my codes

Original

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYxJCoAwEAT/EvAmYZZE49HtFcH/f8MhBhntQwdS1dO1hpXDGFYLE9k7xWkewjV2sVmkCdao6sxu0WaEIpEzhyU95rd2WnIz6tbE/q/4oISIefkjwZY6pH0rA8rYYgE0Y8vPJ2x1VBAtBe4KlLjfXTc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Category = _t, Amount = _t, Share = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Category", type text}, {"Amount", Int64.Type}, {"Share", Percentage.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Index 1-5", each if Number.From(Text.End(Number.ToText([Index]),1)) = 0 then 5
else 

if Number.From(Text.End(Number.ToText([Index]),1))<=5 then
Number.From(Text.End(Number.ToText([Index]),1)) else

Number.From(Text.End(Number.ToText([Index]),1))-5, type number)
in
    #"Added Custom"

Halfway

let
    Source = original,
    #"Filtered Rows" = Table.SelectRows(Source, each [Category] <> null and [Category] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Amount"})
in
    #"Removed Columns"

Final

let
    Source = original,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Category", "Share", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Index 1-5"},halfway,{"Index 1-5"},"halfway",JoinKind.LeftOuter),
    #"Expanded halfway" = Table.ExpandTableColumn(#"Merged Queries", "halfway", {"Category", "Share"}, {"Category", "Share"})
in
    #"Expanded halfway"

[danextian]

Hi @parry2k,

I would have three separate queries for this: the original query and two others that reference to the original

Original 

• Add an index column starting at one
• Add a custom column with the following formula:  

if Number.From(Text.End(Number.ToText([Index]),1)) = 0 then 5
else
if Number.From(Text.End(Number.ToText([Index]),1))<=5 then
Number.From(Text.End(Number.ToText([Index]),1)) else
Number.From(Text.End(Number.ToText([Index]),1))-5

• Change the custom column type to number

This will create a column with numbers that repeat from 1 to 5

Halfway:

• Remove blank rows from either category or share columns

Final

• Remove Category, Share and Custom Column
• Merge Halfway table with Final using custom column created in original
• Expand table to show columns Category and Share
• Then do additional necessary data shaping.