No total for a SUM

[gerardotrejogcg]

I'm calculating correctly the Coop Advertising measure at row context but I'm not able to catch the total.

Basically I want to calculate the Total of Coop Advertising - once performed [Amount USD Real] * SELECTEDVALUE( 'Sales Cluster'[_Coop Advertising] )

Coop Advertising =
VAR _Coop = [Amount USD Real] * SELECTEDVALUE( 'Sales Cluster'[_Coop Advertising] )
VAR _TableCoop = SUMMARIZE ( Sales , Sales[Customer Number], Sales[Cluster Code], "_Coop", _Coop )
VAR _SumTableCoop = SUMX ( CALCULATETABLE ( SUMMARIZE ( _TableCoop, [_Coop] ) ), [_Coop] )
VAR _CoopAdvertising = IF ( HASONEVALUE ( 'Sales Cluster'[_Coop Advertising] ) , _Coop , _SumTableCoop )
Return _CoopAdvertising

I appreciate your help

[gerardotrejogcg]

Many Thanks @OwenAuger it'w working as expecting.   And yes, your assumptions are correct as well.

[OwenAuger · Accepted solution]

Hi @gerardotrejogcg 

First of all, I think you can simplify your measure down to the following:

Coop Advertising =
SUMX (
    SUMMARIZE( Sales, 'Sales Cluster'[_Coop Advertising] ),
    -- Could also use VALUES ( 'Sales Cluster'[_Coop Advertising] )
    [Amount USD Real] * 'Sales Cluster'[_Coop Advertising]
)

This should give the correct result for each row of your visual and at the total level.

I'm assuming there is a many-to-one relationship from Sales to 'Sales Cluster'.

From what I can see of your example, the granularity of the calculation just needs to be the distinct values of 'Sales Cluster'[_Coop Advertising], even if multiple Cluster Codes or Customer Numbers have that same value of _Coop Advertising. I'm also assuming that [Amount USD Real] is additive regardless of grouping.

One issue with your original formula was that the variable _Coop defined at the start has a fixed value after that (unlike a measure), and it appears that in the definition of the variable _TableCoop,  _Coop was expected to behave like a measure.

Does the above give the expected result?

Regards,

Owen