Joined Data Repeats SUM for Each Line

[bmdailey]

Hello,

As this is my first post, please pardon me if it's not quite the standard, but I promise to adapt and catch up quick. I performed a search on other forums within the Power BI message board, but was unable to find an exact match. Please review the associated mockup of this scenario.

Long story short, I have 2 tables that I am using to report project statuses. "Table1" contains a single line for Project#, Costs$ & Budget$. "Table2" contains multiple lines for "SER#" (*key/join), Resource & Billed$.

When I join and report the dollars spent, the final report lists each line and repeats the Billed $ value for each line. The goal is for the Billed $ to show a unique SUM by Project#, instead of showing the grand total of $250 and repeating.

Please advise if there are any clarifying questions that I need to answer.

Thanks for the help,

Byron

[v-chuncz-msft]

@bmdailey,

You could use the following DAX to add a meausre.

Measure =
VAR ProjectId =
    MAX ( Table1[Project#] )
RETURN
    CALCULATE (
        SUM ( Table2[Billed] ),
        FILTER ( Table2, Table2[SER#] = ProjectId )
    )

[bmdailey]

Greetings,

Thank you. I'll create & incorporate this measure into my query this morning & provide the results. The DAX makes sense as it should return the Max Value (*SUM) of the column (ie. if John is billing $150 for 5 business days, it should return SUM of $150 & not SUM of total billed for all resources).

Many regards,

Byron D

[fhill]

When looking at your Relationsihips, under 'Managed Relationships', make sure everything is set to BOTH under the Cross Filter option.  For smaller data sets this just makes joining easier.

Forrest

[bmdailey]

Hi Forest,

Thanks for the heads up. I verified the that the Cross Filter was set as "Both". Interesting enough, I am able to reproduce the multiplier effect using just a few sample rows/columns as provided in the problem statement (*as my original tables have many more).

Thank you,

Byron D