Forum Discussion
SUM value from only the first occurrence
I have two tables ('UserID' and 'User_Balance'). In one - the source of traffic and user ids, in others, user ids and their balances. I create a connection and display into a table just the sums of user balances. But, one user can go from several traffic sources and his balance will be indicated everywhere. As a result, the total amount is considered incorrect. Can I create a measure that calculates the balance amount for the first occurrence only? Thanks!
UserID
| Source | UserID | Date |
| google / cpc | 111 | 01.01.2020 |
| google / organic | 222 | 01.01.2020 |
| google / organic | 111 | 02.01.2020 |
| yahoo / organic | 333 | 03.01.2020 |
| (direct) / (none) | 222 | 03.01.2020 |
User_Balance
| UserID | User_Balance |
| 111 | 123 |
| 222 | 444 |
| 333 | 323 |
Expected Result
| Source | User_Balance |
| (direct) / (none) | |
| google / cpc | 123 |
| google / organic | 444 |
| yahoo / organic | 323 |
Current result
Hi ermak ,
See below:
__New = SUMX(SUMMARIZE(ALL_HITS,ALL_HITS[user_id], "b", IF( CALCULATE(FIRSTNONBLANK(ALL_HITS[date],MIN(ALL_HITS[hits_referer])), ALLEXCEPT(ALL_HITS, ALL_HITS[user_id])) = MIN(ALL_HITS[date]) , MIN('Users Balance'[Balance USD]), BLANK()) ),[b])Link to file.
Please mark as solution if so. Thumbs up for the effort are appreciated.
Kind regards,
Steve.
4 Replies
- stevedep
Memorable Member
Hi ermak ,
See below:
__New = SUMX(SUMMARIZE(ALL_HITS,ALL_HITS[user_id], "b", IF( CALCULATE(FIRSTNONBLANK(ALL_HITS[date],MIN(ALL_HITS[hits_referer])), ALLEXCEPT(ALL_HITS, ALL_HITS[user_id])) = MIN(ALL_HITS[date]) , MIN('Users Balance'[Balance USD]), BLANK()) ),[b])Link to file.
Please mark as solution if so. Thumbs up for the effort are appreciated.
Kind regards,
Steve.
- Greg_Deckler
Community Champion
ermak A little hard to follow. Maybe you want Lookup Min/Max? https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Otherwise, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. Greg_Deckler, thanks for the advice. I changed the first post a little.
I tried the FIRSTNONBLANK function but it doesn't work.sum = SUMX(FIRSTNONBLANK (User_ID[Source],'User_ID'[Source]),SUM(User_Balance[User_Balance]))
