visualization table will only use unique values

[klittle95]

I'm fairly new to Power BI, so it's entirely possible the solution to this is staring me in the face, but I've been working on it since yesterday and I'm out of ideas.

I'm trying to calculate how much money an animal hospital is making over time. I have two very simple tables:

As you can see, together they show what procedures were performed over the last month and how much each costs. I have confirmed that they share a relationship based on ProductID:

All I want is to show how much money they made each month. But when I put the price and date into the same visualization table while making the report, the math is all wrong. I have to leave the day in the table, otherwise some of the rows appear to be duplicates, and the table only counts them once.

Even that doesn't work for the two instances of a procedure costing $15 occurring on January 30th; it only appears once in the table and the math is off accordingly.

I get the closest if I set the Price field to Sum and leave in the days. That way the individual rows are correct. But it totals incorrectly and cannot be used in a line chart. You can see that it seems to think the total is $162, where it should be $732 ($717 in January, $15 in February).

I cannot imagine why using only unique values should be the default. If I go back to the query editor and merge the tables, it works perfectly, but that completely defeats the purpose of having relationships at all.

[v-yulgu-msft]

Hi @klittle95,

Please try to add below measure into table visual together with date field.

Sum Price =
CALCULATE (
    SUM ( Table1[T3-price] ),
    USERELATIONSHIP ( Table1[ProcedureID], Table2[ProcedureID] )
)

Regards,

Yuliana Gu

[klittle95]

Hi @v-yulgu-msft,

I created 

Sum Price = 
CALCULATE (
    SUM ( Table3[T3-price] ),
    USERELATIONSHIP ( Table3[ProcedureID], Table4[ProcedureID] )
)

inside Table3 and added it to each table visual. In every case, it produces the same numbers as Table3[T3-Price] did. 

The same thing happens if I create the measure inside Table4.

[klittle95 · Accepted solution]

You were on the right track--the working code:

Working Price = CALCULATE (  SUMX('ProceduresHistory-1', Related(ProceduresDetails[PD-Price])))