topic Need help getting the correct total on my matrix visual in DAX Commands and Tips

Need help getting the correct total on my matrix visual

KyleFerrero — Sat, 25 Jan 2025 02:01:09 GMT

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!

Re: Need help getting the correct total on my matrix visual

Azadsingh — Tue, 28 Jan 2025 10:22:02 GMT

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.

Re: Need help getting the correct total on my matrix visual

KyleFerrero — Tue, 28 Jan 2025 23:39:51 GMT

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]

)