Need help getting the correct total on my matrix visual

[KyleFerrero]

I have a somewhat complicated measure that essentially distributes a % of the revenue from an opportunity per each campaign that is associated with it. This measure works correctly but I cannot get the total row at the bottom of my matrix to display the correct total. My measure looks like this: 

Attribution Opp Amount (Influence Model) (Contact Level) =

VAR _Campaign = MAX('Influence Model'[CampaignId])

VAR _DistinctCampaigns =  

    CALCULATE(

        DISTINCTCOUNT('Influence Model'[CampaignId]),

        NOT(ISBLANK('Influence Model'[Campaign Rank (Contact Level)])),

        ALL('Influence Model'),

        'Influence Model'[ContactId] = SELECTEDVALUE('Influence Model'[ContactId])

        )

VAR _oppvalue = CALCULATE(MAX('Influence Model'[RevenueShare]), REMOVEFILTERS('Influence Model'[CampaignId]))

VAR _MinCampaignRank =

    MIN('Influence Model'[Campaign Rank (Contact Level)])

VAR _DistributedAmount =

SWITCH(

    TRUE(),

    _DistinctCampaigns = BLANK(), BLANK(),

    _DistinctCampaigns = 1, _oppvalue,

    _DistinctCampaigns = 2,

        SWITCH(

            TRUE(),

            _MinCampaignRank = 1, _oppvalue * 0.5,

            _MinCampaignRank = 2, _oppvalue * 0.5),

    _DistinctCampaigns = 3,

        SWITCH(

            TRUE(),

            _MinCampaignRank = 1, _oppvalue * 0.4,

            _MinCampaignRank = 2, _oppvalue * 0.2,

            _MinCampaignRank = 3, _oppvalue * 0.4),

    _DistinctCampaigns = 4,

        SWITCH(

            TRUE(),

            _MinCampaignRank = 1, _oppvalue * 0.4,

            _MinCampaignRank = 2, _oppvalue * 0.1,

            _MinCampaignRank = 3, _oppvalue * 0.1,

            _MinCampaignRank = 4, _oppvalue * 0.4),

    _DistinctCampaigns = 5,

        SWITCH(

            TRUE(),

            _MinCampaignRank = 1, _oppvalue * 0.4,

            _MinCampaignRank = 2, _oppvalue * 0.0667,

            _MinCampaignRank = 3, _oppvalue * 0.0667,

            _MinCampaignRank = 4, _oppvalue * 0.0667,

            _MinCampaignRank = 5, _oppvalue * 0.4))

RETURN

IF(

    AND(

        ISINSCOPE('Influence Model'[CampaignId]),

        ISINSCOPE('Influence Model'[OpportunityId])

    ),

    _DistributedAmount,

    IF(

        ISINSCOPE('Influence Model'[OpportunityId]),

        _oppvalue,

        BLANK() 

)

)

And my matrix looks like this:




I currently have the total row returning a blank but I want to add a clause after the _oppvalue that would essetially sum down the column, and return values of 36,800, 18,400, and 36,800 respectivly. Any help would be appreciated thanks!

[KyleFerrero · Accepted solution]

Was able to split this into two measures to get the right solution:

Distributed Amount =

VAR _DistinctCampaigns =

    CALCULATE(

        DISTINCTCOUNT('Influence Model'[CampaignLU.Name]),

        REMOVEFILTERS('Influence Model'[CampaignLU.Name])

    )

VAR _MaxRevenue =

    CALCULATE(

        MAX('Influence Model'[RevenueShare]),

        'Influence Model'[Opportunity.Name] = SELECTEDVALUE('Influence Model'[Opportunity.Name]),

        REMOVEFILTERS('Influence Model'[CampaignLU.Name])

    )

VAR _CampaignRank =

    MIN('Influence Model'[Campaign Rank])

RETURN

    SWITCH(

        TRUE(),

        _DistinctCampaigns = BLANK(), BLANK(),

        _DistinctCampaigns = 1, _MaxRevenue,

        _DistinctCampaigns = 2,

            SWITCH(

                TRUE(),

                _CampaignRank = 1, _MaxRevenue * 0.5,

                _CampaignRank = 2, _MaxRevenue * 0.5

            ),

        _DistinctCampaigns = 3,

            SWITCH(

                TRUE(),

                _CampaignRank = 1, _MaxRevenue * 0.4,

                _CampaignRank = 2, _MaxRevenue * 0.2,

                _CampaignRank = 3, _MaxRevenue * 0.4

            ),

        _DistinctCampaigns = 4,

            SWITCH(

                TRUE(),

                _CampaignRank = 1, _MaxRevenue * 0.4,

                _CampaignRank = 2, _MaxRevenue * 0.1,

                _CampaignRank = 3, _MaxRevenue * 0.1,

                _CampaignRank = 4, _MaxRevenue * 0.4

            ),

        _DistinctCampaigns = 5,

            SWITCH(

                TRUE(),

                _CampaignRank = 1, _MaxRevenue * 0.4,

                _CampaignRank = 2, _MaxRevenue * 0.0667,

                _CampaignRank = 3, _MaxRevenue * 0.0667,

                _CampaignRank = 4, _MaxRevenue * 0.0667,

                _CampaignRank = 5, _MaxRevenue * 0.4

            )

    )

Attribution Opp Amount (Influence Model) =

    SUMX(

        SUMMARIZE(

            'Influence Model',

            'Influence Model'[Opportunity.Name],

            'Influence Model'[CampaignLU.Name]

        ),

        [Distributed Amount]

)

[Azadsingh]

Hi @KyleFerrero , Please try this measure and use in your matrix

Attribution Opp Amount (Influence Model) (Contact Level)_new  = SUMX(SUMMARIZE('Table_Name', 'Table_Name'[Contact Id], "Contact Level Total", [Attribution Opp Amount (Influence Model) (Contact Level)),[Contact Level Total])

Contact level total is just a name and I am using your measure as reference to calculate the correct total.

Please try and let me know if it worked for you.