powerbi, sum of distinct value and filter for another column

Question

I have the following table and am trying to get the sum of a particular column. First Table

I would like to take distinct values for the 'TrackerID' and when durationConnected is greater than 0, then take the value greater than 0. In the end I would like to get the sum which in this case is 7. (See second table):

Second Table

I tried creating another table by doing the following: AnotherTableTest = SUMMARIZE(journal;journal[PhoneNumber];journal[StartTime];journal[TrackerID];"UniqueCalls";DISTINCTCOUNT(journal[TrackerID]);"TimeConnected";Max(journal[DurationConnected])). This didn't give me the expected result.

I also tried using a measure: MaxAmount = MAX(journal[DurationConnected]) ActualAmount = SUMX(DISTINCT(journal[TrackerID]);[MaxAmount])

Can anyone help me please?

Marco Vos Marco Vos · Accepted Answer · 2018-10-09T15:08:22

Try creating a new table like this:

newTable = SUMMARIZECOLUMNS('journal'[TrackerID],
    "StartTime", MAX('journal'[StartTime]),
    "totalDuration",SUM('journal'[DurationConnected]))

EDIT
When [DurationConnected] is the only value that changes for each [TrackerID], this answer is a bit cleaner. I also added 'journal'[Outbound] on OP's request (see comments).

newTable = SUMMARIZECOLUMNS('journal'[TrackerID],
    ('journal'[StartTime]),('journal'[Outbound]),
    "totalDuration",SUM('journal'[DurationConnected]))

powerbi, sum of distinct value and filter for another column

1 Answers