Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to sum the count split by categories

Hi, I've been using pbi on and off for 4 years, but still struggle to solve some simple questions. I want to understand how to sum the count of accounts for each region. The desired result is shown in the last column (loaded from excel...)

Hope somebody could help and explain, thanks 🙂

 

  • Ankur04's avatar
    Ankur04
    2 years ago

    Hi,

     

    try writing the below measure 

     

    result = CALCULATE(SUM('Table (4)'[Count of Accounts]),ALLEXCEPT('Table (4)','Table (4)'[Region]))
     
    let me know if this helps.

2 Replies

  • Ankur04's avatar
    Ankur04
    Icon for Resolver II rankResolver II
    2 years ago

    Hi,

     

    try writing the below measure 

     

    result = CALCULATE(SUM('Table (4)'[Count of Accounts]),ALLEXCEPT('Table (4)','Table (4)'[Region]))
     
    let me know if this helps.
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Ankur, it works, thanks for reaching out. Is that a workaround solution for this particular table, or a best practice for that case? Would it work without issues in a table with more columns? My understanding is that ALLEXCEPT in that formula affects row context and sums values for each region, without being affected by different aggregations e.g. "prepared" column. Thank you 🙂