Calculating Totals in different tables - DAX

[Anonymous]

Hi,

I have the following data:

All I am trying to do unsuccessfully is:

I have two tables (calculated ... so not visible in Power Query): ContributionsP and ContributionsR.

All I want to do is for the people in the first column of each table.

Add CountP and CountR together to give a sum.

And I need to know that by Month.

So - take line 1 above:

(ContributionsP) NameP : January : CountP = 3

(ContributionsR) NameR : January : CountR = 7

Therefore and perhaps I need a new table: Contributions

Name : January : 10 (which is the sum of the two).

I have tried SUMMARIZE, SELECTCOLUMNS - but I don't seem to be hitting the right way.

Also the two tables are slightly different - so I was not able to UNION (but again I may have just not done it right).

Any thoughts?

[Anonymous]

Hi v-zhangti,

Your last screenshot is exactly what I want to achieve.

However; when I use the formula you have provided - it gives everyone the same figure and that figure is the total of CountP and CountR.

So in your final picture.

It would say:

A January 23
B February 23
C ... 23

etc..

I should also point out the following in my dataset.

The Names in NameP and NameR will not always match.
i.e. there will be Names in NameP that do not exist in NameR and vice-versa - there will be names in NameR that do not exist in NameP.
Most (90%) will be the same name.

And the Names in both NameP and NameR will repeat for each month of the year.

[v-zhangti]

Hi, @Anonymous 

Please try the following methods.

Table:

Table =
DISTINCT (
    UNION (
        SUMMARIZE ( ContributionsP, [NameP], [Month] ),
        SUMMARIZE ( ContributionsR, [NameR], [Month] )
    )
)

Column:

Count = 
CALCULATE (
    COUNT ( ContributionsP[NameP] ),
    FILTER (
        ContributionsP,
        [NameP] = EARLIER ( 'Table'[NameP] )
            && [Month] = EARLIER ( 'Table'[Month] )
    )
)
    + CALCULATE (
        COUNT ( ContributionsR[NameR] ),
        FILTER (
            ContributionsR,
            [NameR] = EARLIER ( 'Table'[NameP] )
                && [Month] = EARLIER ( 'Table'[Month] )
        )
    )

I adjusted the example data to see if it fits your situation better this time.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

It is defintely doing something a bit more interesting now .. but still not quite.

Here is Table (with NameP):

Here is Table (with NameR):

With your recent option to fix - I get this:

I am not sure how it gets to 41 to be honest and for all rows.

What it should say (for this same person in the Name column is):

Name     Month     Count

A             December   11

A             January     3

A             February    8

A             March        10

A             April          1

Thanks very much for your help.

[v-zhangti]

Hi, @Anonymous 

You can try the following methods. Relate the Month column of the 2 tables.

Table:

Table =
SUMMARIZE (
    ContributionsP,
    [NameP],
    [Month],
    "Count",
        CALCULATE (
            [CountP] + [CountR],
            FILTER (
                ALL ( ContributionsP ),
                [Month] = SELECTEDVALUE ( ContributionsP[Month] )
            )
        )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks. It is still not working - but I'll keep trying and will let you know asap.

[Anonymous]

The problem I have is that ContributionP and ContributionR have different number of columns and so cannot UNION......

[Thejeswar]

Hi @Anonymous ,

You can use the SELECTCOLUMN() DAX to restrict only those column you need before Unioning the table like I have shown below

temptable = UNION(ContributionP, SELECTCOLUMNS(ContributionR, "Month", [MonthR], "Count", [CountR]))

[Thejeswar]

Hi @Anonymous ,

The Way I see it to get this fixed is

1. Union the two tables (Column names should match for the tables)

2. Summarize the Month Column

3. Add the contribution using ADDCOLUMNS() DAX

Something like below

1. Create a Union Table

temptable = UNION(ContributionP, ContributionR)

2. Create another table that groups the data

finaltable = 
SUMMARIZE(temptable, [Month], "NewValue", SUM(temptable[Count]))

This worked for me. You can give it a try

Regards,