Help Creating a calculated row

[Jansco]

Given a table like this:

Product Person Month Value

A	Steve	January	1
A	Steve	February	2
A	Steve	March	3
A	James	January	4
A	James	February	5
B	James	March	6
B	Steve	January	7
B	Steve	March	9

and a Matrix like this

Product Person January February March

A	Steve	1	2	3
A	James	4	5	6
B	Steve	7		9

and the condition, if Month = January, sum January and multiply by .05, if Month = February sum February values and multiply by .75, if Month=March, sum March values and multiply by .25...

Is this possible?  I would like the result to look like this:

Product Person January February March

A	Steve	1	2	3
A	James	4	5	6
B	Steve	7		9
calc		0.6	5.25	4.5
TOTAL		12.6	12.25	22.5

[v-frfei-msft · Accepted solution]

Hi @Jansco ,

One sample for your reference. Please check the following steps as below.

1. Duplicated the fact table in power query , then pviot value column.

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQouSS1LBdJuqUlFpYlFlUCmkVKsDqqkb2JRcgaQNobLeCXmphaD6TyoLhMMOSQjTcGSTkiSMCPN4DIwyxBGmmPIwXRZKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, Steve = _t, January = _t, #"1" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Renamed Columns" = Table.RenameColumns(#"Demoted Headers",{{"Column1", "Product"}, {"Column2", "Name"}, {"Column3", "Month"}, {"Column4", "Value"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Month]), "Month", "Value", List.Sum)
in
    #"Pivoted Column"

2. Create a calculated calculated column in fact table.

Column =
IF (
    'Table1 (2)'[Month] = "January",
    CALCULATE (
        SUM ( 'Table1 (2)'[Value] ),
        ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Month] )
    ) * 0.05
        / CALCULATE (
            COUNTROWS ( 'Table1 (2)' ),
            ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Month] )
        ),
    IF (
        'Table1 (2)'[Month] = "February",
        CALCULATE (
            SUM ( 'Table1 (2)'[Value] ),
            ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Month] )
        ) * 0.75
            / CALCULATE (
                COUNTROWS ( 'Table1 (2)' ),
                ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Month] )
            ),
        IF (
            'Table1 (2)'[Month] = "March",
            CALCULATE (
                SUM ( 'Table1 (2)'[Value] ),
                ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Month] )
            ) * 0.25
                / CALCULATE (
                    COUNTROWS ( 'Table1 (2)' ),
                    ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Month] )
                )
        )
    )
)

3. Enter a new table and create three calculated column in the new table.

January = CALCULATE(SUM('Table1 (2)'[Column]),FILTER('Table1 (2)','Table1 (2)'[Month]="January"))

February = CALCULATE(SUM('Table1 (2)'[Column]),FILTER('Table1 (2)','Table1 (2)'[Month]="February"))

March = CALCULATE(SUM('Table1 (2)'[Column]),FILTER('Table1 (2)','Table1 (2)'[Month]="March"))

4. Union table1 and table2.

Table = UNION(Table1,Table2)

Regards,

Frank