Forum Discussion

bmdailey's avatar
bmdailey
Regular Visitor
9 years ago

Joined Data Repeats SUM for Each Line

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

Need Help

4 Replies

  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

    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's avatar
      bmdailey
      Regular Visitor
      9 years ago
      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
  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    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's avatar
      bmdailey
      Regular Visitor
      9 years ago
      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