Distinct SUM

[Divous]

Hi community,

I have table user_subscription with columns:
user_id - uuid

connector_id (groups) - with two values (bank and manual)

created_at - date in YYYY-MM-DD HH:MM:SS

help_column - created by me with value 1 in every row for easy calculation

My goal is to show cumulative stacked column chart.
I am almost done. My formula is:

Connections = CALCULATE(SUM('public user_subscription'[Help_column]),FILTER(ALLEXCEPT('public user_subscription','public user_subscription'[connector_id (groups)]),'public user_subscription'[created_at] <= MAX('public user_subscription'[created_at])))

and it show nice chart:

Problem what I have is that I dont know how to distinct users by their user_id (unique for user), because many users can have more than one BANK or BANK and MANUAL connections together.

I need show values only per user. How to polish my formula? Or its completly wrong?

Thanks in advance!

[v-kkf-msft · Accepted solution]

Hi @Divous ,

Try the following formula:

Connections = 
CALCULATE(
    SUM('public user_subscription'[Help_column]),
    FILTER(
        ALL('public user_subscription'[created_at]),
        'public user_subscription'[created_at] <= MAX('public user_subscription'[created_at])
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Divous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try like

Connections = CALCULATE(SUMX(values('public user_subscription'[User_id]),calculate(Max('public user_subscription'[Help_column]))),FILTER(ALLEXCEPT('public user_subscription','public user_subscription'[connector_id (groups)]),'public user_subscription'[created_at] <= MAX('public user_subscription'[created_at])))