cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Community Champion

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

12 REPLIES 12
Frequent Visitor

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

Community Champion

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

Frequent Visitor

yeah. Thk you!)

Community Champion

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

Frequent Visitor

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

Community Champion

@analyticsforall my pleasure! 🙂

Frequent Visitor

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

Community Champion

@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

Community Champion

@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``````

Frequent Visitor

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

Community Champion

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

Community Champion

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors