Skip to main content
cancel
Showing results for 
Search instead 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

Reply
analyticsforall
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:

analyticsforall_0-1659076200876.png

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

analyticsforall_1-1659076305270.png

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:

analyticsforall_2-1659076524796.png

 

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.

analyticsforall_4-1659076812020.png

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.

1 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

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

12 REPLIES 12
analyticsforall
Frequent Visitor

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

@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

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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

@analyticsforall my pleasure! 🙂

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

SpartaBI
Community Champion
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

 

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

analyticsforall
Frequent Visitor

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

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

SpartaBI
Community Champion
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.


Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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