how to summarize a measure over many dimension tables

[mr_wizard]

I have a data model with 2 fact tables (say, FactA and FactB) and 2 dimension tables (say DimX and DimY), along with a measure that is a function of both fact tables (say [Variance]).  I need to summarize that measure over fields 'DimX'[Field1] and 'DimY'[Field2], and then I need to compute the sum of the summarized measure.  How can I do so?

I tried the following, but it doesn't give me the result that I expect.

Variance Summarized by Field 1 and Field 2 =

VAR __table =
    ADDCOLUMNS(
        CROSSJOIN(
            DISTINCT('DimX'[Field1]
            ,DISTINCT('DimY'[Field2]       

    )
    ,"Variance Summarized by Field 1 and Field 2"
    ,[Variance]
)

VAR __total =
    SUMX(
        __table

        ,[Variance Summarized by Field 1 and Field 2]

)

RETURN

    __total

[Anonymous · Accepted solution]

Hi @mr_wizard ,

Please try this:

Measure = 
SUMX (
    SUMMARIZE (
        CROSSJOIN ( VALUES ( 'DimX'[Field1] ), VALUES ( 'DimY'[Field2] ) ),
        "Variance Summarized by Field 1 and Field 2", [Variance]
    ),
    [Variance Summarized by Field 1 and Field 2]
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[mr_wizard]

@danextianThat doesn't work.  SUMMARIZECOLUMNS cannot be used in a context transition, i.e., it can't be used in a measure.  I think SUMMARIZE is necessary.  But, SUMMARIZE only works over one table (which is why I thought that CROSSJOIN was necessary).

[danextian]

Hi @mr_wizard 

Try using SUMMARIZECOLUMNS

SUMX (
    SUMMARIZECOLUMNS (
        'DimX'[Field1],
        'DimY'[Field2],
        "Variance Summarized by Field 1 and Field 2", [Variance]
    ),
    [Variance Summarized by Field 1 and Field 2]
)