SUM of values for identical entries in another column/table

[BartVanH]

Hi, please help me figure out this one.

I have two tables with some Service Desk Data.

There's is a working relationship between the two, I don't suspect any issues there.

For each Ticket I know how much Time was spent.

Now I would like to know how much Time was spent for each Customer.

(I guess a Measure is recommended, so I'm not putting my desired results in a table to show you)

So the data I would need:

Cust1: 40

Cust2: 60

Cust3: 170

I have several hundred Customers, so any formula containing a specific value (FILTER ..... 'Cust1') is not applicable.

Thanks!

[v-piga-msft · Accepted solution]

Hi @BartVanH,

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, you also could create the calculated column or the measure with the formula below for Table2.

Measure = CALCULATE(SUM(Table1[Time Spent]),FILTER('Table1','Table1'[Customer ID]=MAX('Table1'[Customer ID])))

Column = CALCULATE(SUM(Table1[Time Spent]),FILTER('Table1','Table1'[Customer ID]=EARLIER('Table2'[Customer ID])))

Best  Regards,

Cherry

[BartVanH]

Thanks for the help!

I was still looking into this.

The first proposed solution works, but only on a visual level.

I actually need to perform more calculations ont the result from filtering Time Spent by Customer, so I would indeed need a measure and/or column.

The second solution seems awesome.

In the meantime, I've been in contact with our ITSM tool admins to actually already perform some of the calculations in the tool. The more I look at it, the more it looks like Power BI is not suited for some of the things I need, like keeping track of certain values across multiple time periods... (and which are not supposed to be done in a reporting tool)

I'll close this question and come back if I get stuck further down the line 🙂

[Stachu]

this will work, you just need to drag the Customer Name and measure in a single visual

Measure = SUM(Table1[Time Spent])

you can put it in any of the tables, model will take care of propagating filters properly