SumOfDeaths measure and when that measure reaches 10

Hi, I am facing a similar problem but would like to asign an index to each date…
Lets say I have a SumOfDeaths meassure and when that meassure reaches 10 or greater, I want to asign that date an index of 1, the next day 2, etc.
thanks

@davidsingular,

Welcome to the forum - great to have you here!

This ended up being a good deal more difficult than I expected, primarily because you need to add a small random component to the cumulative total measure to prevent any ties, while still maintaining the proper order, and also because blanks in the calculation disrupt the ranking. I’m sure there is a more efficient way of doing this, but here’s a solution that I think works for your requirement:

image

These are the two measures that end up doing most of the heavy lifting :

CumulRand = 

CALCULATE(
    ([Total Deaths Plus Rand] ),
    FILTER(
        ALLSELECTED( Dates[Date] ),
        Dates[Date] <= MAX( Dates[Date] )
    )
) 

Conditional Index = 

VAR RawIndex =
CALCULATE(
    IF( [Cumulative Sum of Deaths] >= 10,
        RANKX(
            ALLSELECTED( Dates[Date] ),
            [Cumulative Sum of Deaths],
            [CumulRand],
            ASC,
            Dense
        ),
        BLANK()
    )
)

VAR NumLT10 =
COUNTROWS(
    FILTER(
        ALLSELECTED( Dates[Date] ),
        [Cumulative Sum of Deaths] < 10
    )
)

VAR AdjIndex =
RawIndex - NumLT10 - 1

RETURN
IF(
    AdjIndex > 0,
    AdjIndex,
    BLANK()
)

I hope this is helpful. Full solution file posted below

P.S. @EnterpriseDNA - can you please break David’s question and my response into a separate thread, since it is a distinctly different question from the original. Thanks.

2 Likes

Glad you dropped this into my thread though, I’m interested in knowing ow to do that also

Hi, Thanks so much for this.
And apologies for jumping in into this thead with a related but different topic :slight_smile:
Best, david

@davidsingular,

No worries at all. We just like to break complex threads into separate topics when they start to branch off into distinct issues, to make it easier for people to locate a specific technique/example in the future without having to parse a long, detailed discussion that may be only tangentially related to their current question.

  • Brian