How to calculate value between two queries?

[Kelly_Zhu]

Hi Everyone,

It is hard for me to calculate like this.

 

Any help is appreciable. Thank you in advance!

Regards,

Kelly

[Zubair_Muhammad · Accepted solution]

@Kelly_Zhu

[Zubair_Muhammad]

Hi @Kelly_Zhu

Add this calculated column in Query 2

Custom Value=
Query2[Reference]
    * CALCULATE (
        COUNTROWS ( Query1 ),
        FILTER (
            Query1,
            Query1[First Level Group] = Query2[First Level Group]
                && Query1[Second Level Group] = Query2[Second Level Group]
        )
    )

[Kelly_Zhu]

Thanks a lot!

In addition, Could you tell me how to get the data without calcuation? Thanks in advance!

[MarcelBeug · Accepted solution]

Without calculation it won't be possble.

Possibly you want to know how to do it in Power Query:

let
    Source = Query2,
    #"Merged Queries" = Table.NestedJoin(Source,{"First Level Group", "Second Level Group"},Query1,{"First Level Group", "Second Level Group"},"Query1",JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom value", each [Reference] * Table.RowCount([Query1]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Reference", "Query1"})
in
    #"Removed Columns"

[Zubair_Muhammad · Accepted solution]

@Kelly_Zhu