Totals for unsummarized data (or dealing with repeated values)

[entomophile]

I have a dataset of trap collections.  The dataset has the species collected "species" and the number of nights the trap was running "trap_nights".  One collection can have multiple rows of data where the "trap nights" number is repeated because each species gets its own row.  So, as in the pic below, that one highlighted trap (Bayonne Park) was running for 6 nights, but if I Sum the "trap_nights" it looks like the trap was running for 18 nights becuse it has 3 rows of data.

If I don't Sum "trap_nights" and filter the data in a table, I get the correct numbers, but then I can't get totals.

I can set the "trap_nights" column to minimum, which also gives me the correct values and a total, but the total isn't a sum.  It's just the minimum value for the column.

I also tried the group-by function but then I lose columns I need for other visuals. 

I need to know the total trap nights over a given date range.  How can I calculate the correct trap night numbers considering the repeated values?

[Syk]

Something like...

measure = SUMX(Table,min(trap_nights))

[entomophile]

That doesn't seem to work.  It still sums each row.

[Syk]

Ah yes, you need to group it on the site_name.

Trap Nights =
SUMX ( SUMMARIZE ( NJLT, NJLT[Site_name] ), MIN ( Trap_nights ) )

[entomophile]

Getting closer.  That expression gives the correct numbers, but I still can't get a correct sum.

[Syk]

Looks like it needs the collection date as well.

Trap Nights =
SUMX ( SUMMARIZE ( NJLT, NJLT[Site_name],NJLT[Collection_date] ), MIN ( Trap_nights ) )

[entomophile]

Same issue.  Correct numbers, wrong sum.

[govindarajan_d]

Hi @entomophile 

Try this:

Trap Sum = 
IF(
HASONEFILTER(NJLT[site_name]),
MIN(NJLT[site_name]),
SUMX(VALUES(NJLT[site_name]),MIN(NJLT[site_name])
)
)

[entomophile]

If my syntax is correct, that expression does not work.

[govindarajan_d · Accepted solution]

Hi @entomophile,

Sorry about that. I had used the wrong column name in the formula 😅. Try this:

Trap Sum = 
IF (
    HASONEFILTER ( NJLT[site_name] ),
    MIN ( NJLT[trap_nights] ),
    SUMX (
        SUMMARIZE (
            NJLT,
            NJLT[site_name],
            NJLT[collection_date],
            "MinOfValues", MIN ( NJLT[trap_nights] )
        ),
        [MinOfValues]
    )
)

[entomophile]

That's it!  Thank you so much, I never would have figured this out on my own.

[govindarajan_d]

Hi @entomophile,

Happy to help! 😊