SUM of Total Differences and keep all filter

[tian]

Hi,

I have data like this:

category = dim table
value last week = calculation table
value current week = calculation table
diff = calculation table

category	value last week	value current week	Diff	sum of diff
a	88%	88%	0.00%	?
b	87%	90%	3.00%	?
c	0%	0%	0.00%	?
a	69%	70%	1.00%	?

I need the value SUM of total diff (0%+3%+0%+1%) = 4%, the result should be:

category	value last week	value current week	Diff	sum of diff
a	88%	88%	0.00%	4%
b	87%	90%	3.00%	4%
c	0%	0%	0.00%	4%
a	69%	70%	1.00%	4%

I try to create a measure but the result is still incorrect
my measure = 

CALCULATE(SUMX(VALUES('dim table'[category]),[Diff]),ALLSELECTED('dim table'[category]))

what measure should I create?

I hope i'm clear enough.

Thank you guys

[v-yingjl · Accepted solution]

Hi @tian ,

You can try this measure:

sum of diff =
VAR tab =
    SUMMARIZE (
        'calculation table',
        'calculation table'[value current week],
        'calculation table'[value last week],
        "A",
            CALCULATE (
                SUM ( 'calculation table'[value current week] ),
                ALL ( 'calculation table' )
            )
                - CALCULATE (
                    SUM ( 'calculation table'[value last week] ),
                    ALL ( 'calculation table' )
                )
    )
RETURN
    SUMX ( tab, [A] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

Is the category field in the visual from the dimension table?

please post some sample data

[PaulDBrown]

what is your [Diff] measure?

If [Diff] is delivering the correct total, then:

all diff = CALCULATE([Diff], ALL(Dim table[category]))

if it isn't, first create the SUMX measure and then:

all diff= CALCULATE([Sumx measure], ALL(Dim table[category]))

[tian]

I have try your suggestion, 

first I create the SUMX measure and then:

all diff= CALCULATE([Sumx measure], ALL(Dim table[category]))

but the result is total sumx not total sum all percentage of diff. not 4%.

[PaulDBrown]

Try:

all diff = 

CALCULATE(

SUMX(VALUES('dim table'[category]),[Diff]),

ALL('dim table'[category]))

[tian]

Thank you for the response, the result still incorrect same as my measure before.