calculate a measure on two dimensions from two fact tables, but partly exclude one of the dimensions

[akos_skutovics]

Hi, 

I would like to calculate a measure on two dimensions from two fact tables, but for one part of the calculation, I want to exclude one of the dimensions.

 
measures:

Fact1Sum = SUM(Fact1[amount])

Fact2Sum = SUM(Fact2[amount])

SumAll = [Fact1Sum] - [Fact2Sum]

I want to multiply 'SumAll' with 'Fact2Sum' but I want to exclude 'dim20' from the 'SumAll' calculation

 
something like this but it doesnt quite work:

Measure1 = CALCULATE([SumAll], ALL(Fact2[dim20_id])) * [Fact2Sum]

what I would like to achieve:

First, I tried summarizecolumns, but it gives "SummarizeColumns() and AddMissingItems() may not be used in this context" error message when put together with the dimentions in a table visual.

Table = SUMX(

    SUMMARIZECOLUMNS(

        Dim10[dim10_id],

        Dim20[dim20_id],

        "_measure", CALCULATE([SumAll], ALL(Fact2[dim20_id])) * [Fact2Sum]

    ),

    [_measure]

)

Then I created a table that works, but when I used it as a measure (by encapsulating the code below in a SUMX function), the measure did not create the correct values. Also, the crossjoin is a really heavy function to use. There must be a better way to do this. Does anyone have a suggestion on how to make this the right way?

SUMMARIZE(

    FILTER(

        CROSSJOIN(

            SUMMARIZE(

                Dim10,

                Dim10[dim10_id],

                "_sumall", [SumAll]

            ),

            SUMMARIZE(

                Fact2,

                Fact2[dim10_id_f],

                Fact2[dim20_id],

                "_fact2sum", [Fact2Sum]

            )

        ),

        Dim10[dim10_id] = Fact2[dim10_id_f]

    ),

    [dim10_id],

    Fact2[dim20_id],

    [_sumall],

    [_fact2sum],

    "measure", [_sumall] * [_fact2sum]

)

since I cannot share the pbix file (such a shame) here is the data:

Fact1:

fact1_id;dim10_id;amount
1;1;1
2;2;2
3;2;1,5

Fact2:
fact2_id;dim10_id_f;dim20_id;amount
1;1;3;2
2;1;4;2
3;2;3;1
4;2;4;2

Dim10
dim10_id;dim10_name
1;name1
2;name2

Dim20
dim20_id;dim20_name
3;name3
4;name4

[johnt75 · Accepted solution]

Create a couple of measures,

Measure 1 =
VAR SummaryTable =
    ADDCOLUMNS (
        VALUES ( Dim10[dim10_name] ),
        "@val",
            VAR SumAll =
                CALCULATE ( [SumAll], REMOVEFILTERS ( Dim20 ) )
            VAR Fact2Sum = [Fact2Sum]
            RETURN
                SumAll * Fact2Sum
    )
RETURN
    SUMX ( SummaryTable, [@val] )

[akos_skutovics]

Thank you @johnt75  and @PaulDBrown. Both of your comments corrected my mistake and helped me solve my issue.
I used ALL(Fact2[dim20_id]) instead of ALL(dim20)

[PaulDBrown · Accepted solution]

I'm not too sure what you're definition of 

"I want to exclude 'dim20' from the 'SumAll' calculation",

but see if this is what you are after (I'm posting all the measures so you can correct if a step is the wrong calculation):

Sum f1 = 
SUM('fact 1'[amount])

Sum f2 = 
SUM('fact 2'[amount])

Exclude dim20 = 
CALCULATE([Sum f2], ALL(dim20))

SumALL = 
[Sum f1] - [Exclude dim20]

Final Measure = 
SUMX(dim10, [SumALL] * [Sum f2])

To get:

[johnt75 · Accepted solution]

Create a couple of measures,

Measure 1 =
VAR SummaryTable =
    ADDCOLUMNS (
        VALUES ( Dim10[dim10_name] ),
        "@val",
            VAR SumAll =
                CALCULATE ( [SumAll], REMOVEFILTERS ( Dim20 ) )
            VAR Fact2Sum = [Fact2Sum]
            RETURN
                SumAll * Fact2Sum
    )
RETURN
    SUMX ( SummaryTable, [@val] )