Count of IDs meeting a certain criteria (sum of values)

[DJBAJG]

Hello community

This is the first of many (I'm sure) questions that I'll have. Thanks for your help in advance.

Probably a pretty simple one but I'm looking to create a measue that counts the number of clients, within a certain time period, that met a certain threshold of dollars of purchases. These clients make many purchases during the time period in question so I assume I'd need to sum the dollars at a client level, attribute that amount to a unique client ID and then count those unique clients all while constraining based on time period. If I can achieve that it will be sufficient but...

What I really want to be able to do is everything as stated above but also include a slicer that allows for the selection of the threshold amonut. Probably not simple anymore but hopefuly I'm wrong.

Thanks. 

[v-lid-msft · Accepted solution]

Hi @DJBAJG ,

We can use the following measures to meet your requirement:

BaseCount =
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( 'Enrollment'[Member ID], "P", [Paid] ),
        [P] > 100000
    )
)

PreviousCount =
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( 'Enrollment'[Member ID], "P", [Paid Prior 1] ),
        [P] > 100000
    )
)

CurrentPercent =
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Enrollment'[Member ID],
            "P", [Paid],
            "s", MAX ( 'Enrollment'[Member Status] )
        ),
        [P] > 100000
            && [s] IN FILTERS ( 'Enrollment'[Member Status] )
    ),
    [P]
)
    / SUMX ( SUMMARIZECOLUMNS ( 'Enrollment'[Member ID], "P", [Paid] ), [P] )

PreviousPercent =
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Enrollment'[Member ID],
            "P", [Paid Prior 1],
            "s", MAX ( 'Enrollment'[Member Status] )
        ),
        [P] > 100000
            && [s] IN FILTERS ( 'Enrollment'[Member Status] )
    ),
    [P]
)
    / SUMX ( SUMMARIZECOLUMNS ( 'Enrollment'[Member ID], "P", [Paid Prior 1] ), [P] )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DJBAJG]

Thank you both danextian and v-lid-msft. I attempted both of your solutions with varying success but neither one got me where I need to be probably because I didn't give enough detail. I've attached a .pbix file that has a sample of the data I'm working with so you can see what really needs to be done. 

The slicer in the red circle is the threshold filter (based on the total paid for the BASE (Current) time period) for all visuals on the page. It needs to function like the other two slicers above it meaning it filters everything. The text boxes in green need to be cards displaying the values I spell out in each. I need a count of the current members based on the all of the slicers, a count of the previous member based on all of the slicers and then two % values showing how much the displayed values (based on the slicers) contribute to the total paid amount of the total claims table (for the time period selected, which is displayed at the top of the page). 

This is why I initially stated it was complicated. I was trying to simplify it and then build it back up in my specific application but I didn't provide enough detail so sorry about that.

Again, any help is greatly appreciated. 

.pbix file is here. Just an FYI it's about 30MB

https://drive.google.com/open?id=13woYGiUU9RvTBz1TVCYIvJVAQtBr3g3k

[DJBAJG]

Is any more detail required? I'm really stuck here and need the help. 

[v-lid-msft · Accepted solution]

Hi @DJBAJG ,

We can use the following measures to meet your requirement:

BaseCount =
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( 'Enrollment'[Member ID], "P", [Paid] ),
        [P] > 100000
    )
)

PreviousCount =
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( 'Enrollment'[Member ID], "P", [Paid Prior 1] ),
        [P] > 100000
    )
)

CurrentPercent =
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Enrollment'[Member ID],
            "P", [Paid],
            "s", MAX ( 'Enrollment'[Member Status] )
        ),
        [P] > 100000
            && [s] IN FILTERS ( 'Enrollment'[Member Status] )
    ),
    [P]
)
    / SUMX ( SUMMARIZECOLUMNS ( 'Enrollment'[Member ID], "P", [Paid] ), [P] )

PreviousPercent =
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Enrollment'[Member ID],
            "P", [Paid Prior 1],
            "s", MAX ( 'Enrollment'[Member Status] )
        ),
        [P] > 100000
            && [s] IN FILTERS ( 'Enrollment'[Member Status] )
    ),
    [P]
)
    / SUMX ( SUMMARIZECOLUMNS ( 'Enrollment'[Member ID], "P", [Paid Prior 1] ), [P] )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DJBAJG]

Thank you so much v-lid-msft. This is nearly the entire solution. The only part that is still needed is the ability to dynamically control the threshold amount. You hard coded it as 100,000 but I need a slicer, the HCC Threshold slicer, to dynamically switch between values, for example 50,000, 100,000 and 250,000. Is this as simple as replacing your "P" in the code with a reference to a parameter value from a table containing the values I need? 

Thanks again. You've gotten me much farther than I could have gotten on my own and I'm learning valuable skills. 

[DJBAJG]

Ok, I figured out the threshold filtering. I can now control the new card values based on my selection from the slicer. The last piece I'm having trouble with is getting my new slicer to interact with the current Member ID visuals. I'm quite certain this is pretty simple but one last piece of help would be great.

Again, I very muc happreciate the help. This has been great so far.

.pbix linked here

[v-lid-msft · Accepted solution]

Hi @DJBAJG ,

We can create a measure and put it into the visual Filter, then set condition >0 to meet your requirement.

IsPaidOverThreshold = [Paid]-[HCC Threshold Value]

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @DJBAJG ,

Firstly, we can create a parameter as threadhold,

Then we can create a measure to meet your requirement:

Count = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            DISTINCT ( 'Table'[ClientsID] ),
            "TotalAmount", CALCULATE (
                SUM ( 'Table'[Amount] ),
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ClientsID] = EARLIER ( [ClientsID] ) )
            )
        ),
        [TotalAmount] > threshold[threshold Value]
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Try these:

Calculated columns - 

DollarSum =
CALCULATE (
    SUM ( table[amount] ),
    ALLEXCEPT ( table, table[ID], table[period] )
)

DollarGrouping =
SWITCH (
    TRUE (),
    table[DollarSum] > 1000, "over 1000",
    table[DollarSum] > 100, "101 to 999",
    "< 100"
)

For the count of ID's, try either depending on your need as a measure - 

Count of ID meeting criteria =
CALCULATE (
    DISTINCTCOUNT ( table[ID] ),
    FILTER ( table, table[DollarGrouping] = "over 1000" )
)

Count of ID meeting criteria =
CALCULATE (
    DISTINCTCOUNT ( table[ID] ),
    FILTER ( table, table[DollarSum] >= 500 )
)