filter clients by ARPU, where each client had at least 1 month with sum of rev. filtered by param

[analyticsforall]

Hello, pbi friends,

I have a table with clients (users) and their revenue in USD per month:

In pbi report I have a parameter with values from 1 to 1 000 that will be a filter for clients revenue per month.

As a result, I need to have a table with only those clients who had revenue at least in one period >= than sum in parameter per month.

For example, if I enter 1 000, as a result I should have table only with clients 1 and 2, because only they have monthly revenue >= 1 000:

I created such measure, but it gave me wrong result, because it didn't check amount per user and month that fits the condition in parameter.

It seems I need to create some measures inside each other with "summarize" constructions.

Could you please help me with this with any ideas?

pbix file 

Thanks in advance.

[analyticsforall]

@SpartaBI I'm in a meeting and will be waiting for you, my friend) Please, join as soon as possible.

[SpartaBI]

Hey friend, will be free in 30 minutes. Is that ok?

[analyticsforall]

yeah. Thk you!)

[SpartaBI · Accepted solution]

@analyticsforall 

Revenue USD net by ARPU = 
VAR _SelRev =
    SELECTEDVALUE ( ARPU[ARPU] )
VAR _MxDt =
    MAX ( 'Table'[Date] )
VAR _clients_and_revenues =
    FILTER(
        ALL('Table'),
        CALCULATE(SUM('Table'[Amount USD]) >= _SelRev)
    )
VAR _clietns = 
    SELECTCOLUMNS(
        _clients_and_revenues,
        "@User", 'Table'[User]
    )
VAR result = 
    CALCULATE(
        SUM('Table'[Amount USD]),
        KEEPFILTERS(_clietns)
    )
RETURN
result

      

Showcase Report – Contoso By SpartaBI

[analyticsforall]

this is exactly what I need! Thank you very much. Following you on LinkedIn 🙂 

[SpartaBI]

@analyticsforall my pleasure! 🙂

[analyticsforall]

@SpartaBI Thank you so much for your time. However, this is not exactly the result I need. I need that all revenue for the client should be returned if an ARPU match was found in one of months.

It would be great, if we look together at this case at the meeting!)

If this time is convenient for you, please connect. Or write at what time it would be convenient.

Friday, July 29 · 2:00 – 3:00pm (UTC+3)
Video call link: https://meet.google.com/mnp-ysvt-dfd

[SpartaBI]

@analyticsforall sure, will be free in an hour and will ping you, or aroud the time you suggested. Anyway in the next couple of hours. Will ping here once ready

[SpartaBI]

@analyticsforall is this what you meant?:

filter clients by ARPU, where each client had at least 1 month with sum of rev. filtered by param 20...

Revenue USD net by ARPU = 
VAR _SelRev =
    SELECTEDVALUE ( ARPU[ARPU] )
VAR _MxDt =
    MAX ( 'Table'[Date] )
VAR _clients_and_revenues =
    FILTER(
        ALL('Table'[User],'Table'[Date]),
        CALCULATE(SUM('Table'[Amount USD]) >= _SelRev)
    )
VAR _clietns = 
    SELECTCOLUMNS(
        _clients_and_revenues,
        "@User", 'Table'[User]
    )


VAR result = 
    CALCULATE(
        CALCULATE(SUM('Table'[Amount USD])),
        KEEPFILTERS(_clietns) 
    )
RETURN
result

      

Showcase Report – Contoso By SpartaBI

[analyticsforall]

@SpartaBI hi!) attached to the message. thank you.

[SpartaBI]

@analyticsforall great :)!
Do you want to do a quick zoom / teams with me to look together?
Will save us some time 

[SpartaBI]

@analyticsforall hey friend, if you share a sample of your PBIX (deopbox/gdrive/one drive) I could send you back one with the result.
Very hard to do this with screenshots.