Forum Discussion

ysherriff's avatar
ysherriff
Resolver II
3 years ago
Solved

Fix Matrix total based on two rows

Hi,

I am having difficulty coming with a formula to correct my totals in a table with multiple rows. As an example, in image #1, this is the correct total with only one value in the Row.  In Image #2, when I add a date hierarchy, the total is incorrect. Image #3 is the formula I am using. 

Can anyone help? Thaks in advance

 

Image#1

 

 

 

Image #2

 

 

 

 

image #3

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi ysherriff 

    please try

     

    =
SUMX (
    SUMMARIZE ( Campaigns, Campaigns[Campaign Name], 'Date'[Year], 'Date'[Otr-Yr] ),
    CALCULATE (
        SELECTEDMEASURE (),
        CROSSFILTER ( Campaigns[Contact ID], 'Contacts Table'[Contact ID], BOTH )
    )
)

     

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi ysherriff 

    please try

     

    =
SUMX (
    SUMMARIZE ( Campaigns, Campaigns[Campaign Name], 'Date'[Year], 'Date'[Otr-Yr] ),
    CALCULATE (
        SELECTEDMEASURE (),
        CROSSFILTER ( Campaigns[Contact ID], 'Contacts Table'[Contact ID], BOTH )
    )
)

     

    • ysherriff's avatar
      ysherriff
      Resolver II
      3 years ago

      Perfect. Works perfectly.

       

      Thank you very much ğŸ˜€