How to show the total value of B table for column in A table based on linked key connection

[Anonymous]

Dear all,

I'm very new to Power BI, really need help for the below request.

I have data like below :

And what I want to get in front-end is :

The logic is :

Column1 is just the sum(Value1), that's not hard I think.

Column2 is sum(Value2), but it's based on the Table2.D2 = Table1.D2

300 = 100(D2 = AA) + 200(D2 = AB)

700 = 300(BB) + 400(BC),  because there is no D2 = BD in table2.

1100 = 500 + 600, table 1 is main table, so even we have D2 = CE in table2, we won't add it.

I hope I have clarified what I want.

Thanks advanced and any questions please tell me.

Actually I create a sample for you, but I don't know how to upload it to the thread...

Aiolos Zhao

[Mariusz]

Hi @Anonymous 

It's fairly simple, please see the M code below three querie

// Table1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIEEYYGSrE6UL4TkDCC8EFMJxBhjMR3BhImSHwXIGEK4YOknEGEGRIfJG8O5McCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, Value1 = _t])
in
    Source

// Table2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIEEYYGBkqxOlABJyBhBBUAsZ1AhDGygDOQMIEKgNjOIMIUWcAFSJghC7gCCXOQQCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, Value2 = _t])
in
    Source

// Merge2
let
    Source = Table.NestedJoin(Table1, {"D2"}, Table2, {"D2"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Value2"}, {"Value2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table2",{{"Value1", Int64.Type}, {"Value2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"D1"}, {{"Value1", each List.Sum([Value1]), type text}, {"Value2", each List.Sum([Value2]), type text}})
in
    #"Grouped Rows"

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Mariusz ,

Really thanks for your reply, I got your point, but it's my fault, I miss a problem when I create the data.

I change the Table 1 data to below(didn't change Table 2 data, already left join Table 2) 

You can see after joining, we have two 200 in Value2, and 2 600 in Value2, it will be duplicate when I sum it in front-end.

How to solve this problem?

Thanks.

Aiolos

[Mariusz]

Hi @Anonymous 

1. You can you Group by one both tables and Merge after.
2. You can Merge on more than one column, lets say 2 columns make up a unique vale than you can select both surring the merge process.

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @Mariusz ,

I can't group by the D3, because I also want to do the filter in the front-end using D3.

If the value2 are same in different D2, like AA = 100, AB = 100, can I use unique value to do it?

If what I understand is wrong, please tell me, because I'm new to PBI.

Thanks.

Aiolos Zhao

[Mariusz]

Hi @Anonymous 

Still not realy sure about what you want ot achive.

To avoid duplicates you need to Group By Table one on D1 and D2, that means losing D3 like below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXKEECDS0EApVgcq6gQknJxApBGaqLOzM5C0gIhClMFFjZFEQXwXFxcgaYIk6gI31xQi6gzWDDfBDEnUBe4yczRRiLmWQNFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, D3 = _t, Value1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"D1", "D2"}, {{"Value1", each List.Sum([Value1]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"D2"}, Table2, {"D2"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Value2"}, {"Value2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table2",{{"Value2", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type1", {"D1"}, {{"Value1", each List.Sum([Value1]), type number}, {"Value2", each List.Sum([Value2]), type number}})
in
    #"Grouped Rows1"

if you need to keep D3 for some reason you need to consider adding it to table 2 to make it unique or using Measure to calculate Value 2

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Mariusz ,

The Excel screenshot is what I want.

I think I have closed the result using below DAX:

Measure = SUMX(SUMMARIZE(Table1,Table1[D2],"test",SUM(Table1[Table2.Value2]) / COUNT(Table1[Table2.Value2])),[test])

There is one question left, when I using the DAX, and filter the D3 in the front-end, the Measure will be changed,

how to change the DAX so I can avoid the changing by filter D3?

If you have any other good ideas please tell me.

Thanks.

Aiolos Zhao

[Mariusz]

Hi @Anonymous 

This should do the trick

Measure = 
SUMX(
    SELECTCOLUMNS(
        ALLEXCEPT(Table1, Table1[D2], Table1[D1]),
        "test", CALCULATE(AVERAGE(Table1[Table2.Value2]))
    ),
    [test]
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Mariusz ,

Sorry for reply late, I have tried your DAX, it's a little wrong I think, seems it should be:

Measure = 
SUMX(
    SELECTCOLUMNS(
        ALLEXCEPT(Table1, Table1[D3], Table1[D1], Table[Value1]),
        "test", CALCULATE(AVERAGE(Table1[Table2.Value2]))
    ),
    [test]
)

but if I have too many columns need to except, is there any better way to solve that?

Thanks.

Aiolos

[Mariusz · Accepted solution]

Hi @Anonymous 

The below should give you same result.

Measure 1 = 
SUMX(
    SELECTCOLUMNS(
        ALL(Table1[D2]),
        "test", CALCULATE(AVERAGE(Table1[Table2.Value2]))
    ),
    [test]
)

The below will give you same result but will not react to D3 filter.

Measure Two = 
SUMX(
    GROUPBY(
        CALCULATETABLE(
            Table1,
            ALLEXCEPT(
                Table1,
                Table1[D1],
                Table1[D2]
            )
        ),
        Table1[D2],
        "avg", AVERAGEX(CURRENTGROUP(), Table1[Table2.Value2])
    ),
    [avg]
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I think the final DAX is :

Measure = CALCULATE(SUMX(SUMMARIZE(Table1,Table1[D2],"test",SUM(Table1[Table2.Value2]) / COUNT(Table1[Table2.Value2])),[test]),ALL(Table1[D3]))

is there any better way to solve that?