Forum Discussion

AndyMeiks's avatar
AndyMeiks
Frequent Visitor
2 years ago
Solved

Card Showing Sum of Distinct Values

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. 

 

Nameperiod_nametotal_period_time
AQuarter 110
BQuarter 110
CQuarter 110
AQuarter 212
BQuarter 212
CQuarter 212
AQuarter 38
BQuarter 38
AQuarter 410
CQuarter 410

 

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.

General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    2 years ago

    Hi AndyMeiks ,

     

    Try this:

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

     

2 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi AndyMeiks ,

     

    Try this:

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

     

    • AndyMeiks's avatar
      AndyMeiks
      Frequent Visitor
      2 years ago

      Perfect, exactly what I wanted. Thankyou.