Card Showing Sum of Distinct Values

[AndyMeiks]

Hi team,

Hoping you can help with this - I've seen similar solutions for this but none that are quite perfect:

I'm wanting to display a basic Card visual that summarises the unique values in a column. I have sports data broken down by period, e.g. Quarter 1, and want to show a value for 'Total Time'. Because the total_period_time exists for each individual athlete, a simple sum doesn't work. 

Name	period_name	total_period_time
A	Quarter 1	10
B	Quarter 1	10
C	Quarter 1	10
A	Quarter 2	12
B	Quarter 2	12
C	Quarter 2	12
A	Quarter 3	8
B	Quarter 3	8
A	Quarter 4	10
C	Quarter 4	10

So my expected value for the card would be 10 + 12 + 8 + 10. However - the additional complication:

I then want to be able this to be filterable by selecting different numbers of periods:

E.g. the expected value in the card would be 10 + 12. I considered filtering to only 1 athlete (e.g. A), however A won't always play every quarter nor every match.

How would I best achieve this? Open to any suggestions. Let me know if I can provide more info.

[danextian · Accepted solution]

Hi @AndyMeiks ,

Try this:

sum =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[period_name], 'Table'[total_period_time] ),
    [total_period_time]
)

[AndyMeiks]

Perfect, exactly what I wanted. Thankyou.