Forum Discussion

Divous's avatar
Divous
Helper III
5 years ago
Solved

Distinct SUM

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!

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    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.