Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Thanks in advance.
Solved! Go to Solution.
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
@SpartaBI I'm in a meeting and will be waiting for you, my friend) Please, join as soon as possible.
Hey friend, will be free in 30 minutes. Is that ok?
yeah. Thk you!)
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
this is exactly what I need! Thank you very much. Following you on LinkedIn 🙂
@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
@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
@analyticsforall is this what you meant?:
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
@analyticsforall great :)!
Do you want to do a quick zoom / teams with me to look together?
Will save us some time
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
72 | |
49 |
User | Count |
---|---|
142 | |
139 | |
110 | |
69 | |
55 |