Help Creating a calculated row

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