cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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] )

19.PNG


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.

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.

View solution in original post

8 REPLIES 8
Highlighted
Super User II
Super User II

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 )
)

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Community Support
Community Support

Hi @DJBAJG ,

 

Firstly, we can create a parameter as threadhold,

 

19.PNG

 

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]
    )
)

20.PNG21.PNG

 

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.

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.
Highlighted
Helper II
Helper II

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). 

 

 

HCC Instructions.PNG

 

 

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

 

Highlighted

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

Highlighted

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] )

19.PNG


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.

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.

View solution in original post

Highlighted

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. 

Highlighted

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

Highlighted

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]

1.PNG

 

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.

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors