locked
formula for summed filter RRS feed

  • Question

  • I have a table that has sales (primary key) and customers (the same customer can have multiple claims). 

    I created a formula that does claim count and when I put customers on rows in a pivot table it shows count per customers. How can I write a formula that says only show customers with a claims count above a certain amount? I can filter this in the pivot table but I was curious if there was a way to filter this in a measure. Thanks. 

    Wednesday, November 23, 2016 7:13 PM

Answers

  • How does the distinct part work exactly? Sumx evaluates row by row, right? 

    Yes, SUMX effectively loops over the table or table expression in the first argument and evaluates the scalar expression in the second argument and sums the result as it goes.

    When you insert distinct it evaluates each unique item together right, but then how does it know to count ALL the rows for each unique set vs. just evaluating it row by row like it would if there was no distinct formula there? Thanks. 

    SUMX still counts rows, the "trick" is that DISTINCT returns a table object. A temporary in-memory table that only exists while the expression is being evaluated. (the actual evaluation may or may not actually materialize a temporary table, but it's an appropriate mental model)

    So if you had a table called MyTable with 2 columns like the following:

    Col1    Col2
    1         A
    2         A
    2         A
    3         B

    And a measure [Sum of Col1] which is just =SUM(MyTable[Col1])

    Then =SUMX(MyTable, [Sum of Col1]) would loop over all 4 rows and would do 1+2+2+3 = 8

    While SUMX(Distinct(MyTable[Col2], [Sum of Col1]) would only loop over a virtual table with 2 rows containing the values A and B and would produce 5+3 = 8.

    So you can see how if you have an IF expression it will evaluate in the context of the virtual DISTINCT table.

     


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 29, 2016 11:41 AM

All replies

  • You don't mention what you want to show for the customers with a claim count above a certain amount. But assuming that you wanted to show the results of another measure (like [Claim Total] you could do that using a SUMX() with an IF check in the expression to test the value of the [Claim Count] measure

    eg.

    =SUMX( Distinct(MyTable[Customer]) , IF( [Claim Count] > 5, [Claim Total] ) )


    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 23, 2016 8:28 PM
  • Hi AlexMartini,

    We are not able to only show customers with a claims count above a certain amount in measure. Because measure is used calculate aggregation according to the rows in table. We only filter the rows used to calculate rather than the row shown in pivot table. It will show all the customer when you select the customer in row level. 

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 25, 2016 1:51 AM
  • We are not able to only show customers with a claims count above a certain amount in measure. Because measure is used calculate aggregation according to the rows in table. We only filter the rows used to calculate rather than the row shown in pivot table. It will show all the customer when you select the customer in row level. 

    Hi Angela, your statements here are not correct. Because the default behaviour of pivot tables is to not show rows with empty values you will find that the approach I suggested in my previous answer of combining SUMX and IF should achieve the desired outcome.


    http://darren.gosbell.com - please mark correct answers

    Friday, November 25, 2016 3:28 AM
  • Hi Darren, 

    Thanks, that's cool and it worked. How does the distinct part work exactly? Sumx evaluates row by row, right? When you insert distinct it evaluates each unique item together right, but then how does it know to count ALL the rows for each unique set vs. just evaluating it row by row like it would if there was no distinct formula there? Thanks. 

    Monday, November 28, 2016 6:52 PM
  • How does the distinct part work exactly? Sumx evaluates row by row, right? 

    Yes, SUMX effectively loops over the table or table expression in the first argument and evaluates the scalar expression in the second argument and sums the result as it goes.

    When you insert distinct it evaluates each unique item together right, but then how does it know to count ALL the rows for each unique set vs. just evaluating it row by row like it would if there was no distinct formula there? Thanks. 

    SUMX still counts rows, the "trick" is that DISTINCT returns a table object. A temporary in-memory table that only exists while the expression is being evaluated. (the actual evaluation may or may not actually materialize a temporary table, but it's an appropriate mental model)

    So if you had a table called MyTable with 2 columns like the following:

    Col1    Col2
    1         A
    2         A
    2         A
    3         B

    And a measure [Sum of Col1] which is just =SUM(MyTable[Col1])

    Then =SUMX(MyTable, [Sum of Col1]) would loop over all 4 rows and would do 1+2+2+3 = 8

    While SUMX(Distinct(MyTable[Col2], [Sum of Col1]) would only loop over a virtual table with 2 rows containing the values A and B and would produce 5+3 = 8.

    So you can see how if you have an IF expression it will evaluate in the context of the virtual DISTINCT table.

     


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 29, 2016 11:41 AM