Search code examples
countpowerbidaxpowerbi-desktopgroup

Count rows of groups after sorting data in Power BI

I have a table with the following columns:

  • different products: A, B, C...
  • date and time of purchase.

I have sorted the table to arrange the data in descending order of date, latest to earliest. I want to count the number of same products that are purchased together. For example: after sorting the table, my products column will look like A,A,B,B,B,B,C,C,C,C,C,C,B,B,B,A,A,A,A,C... What I want is to group them n count the number of same products that are together:

  • A - 2
  • B - 4
  • C - 6
  • B - 3
  • A - 4
  • C - 1...

Does anyone know how to do this in Power BI? Even if there are multiple purchases of the same products back to back on different dates, I still want them all (same products) grouped together to count them. Thanks in advance.

Solution

  • Once you ordered your data, add an index column in power query editor as shown below-

    enter image description here

    now create a Measure as below-

    consecutive_count = 

var current_row_product = MAX('your_table_name'[product])
var current_row_index = MAX('your_table_name'[Index])
var next_row_index = max('your_table_name'[Index]) + 1

var next_row_product = 
CALCULATE(
    MAX('your_table_name'[product]),
    FILTER(
        ALL('your_table_name'),
        'your_table_name'[Index] = next_row_index
    )
)

var min_consecutive_product_index = 
CALCULATE(
    MAX('your_table_name'[Index]),
    FILTER(
        ALL('your_table_name'),
        'your_table_name'[Index] <= current_row_index
        && 'your_table_name'[product] <> current_row_product
    )
) + 0

return 
if(
    next_row_product <> current_row_product, 
    current_row_index - min_consecutive_product_index
)

    Now add Index, product and new measure to a table visuala and final output will be as below-

    enter image description here

    First column Index may looks annoying but you can not remove. But you can handle that using 0 width for the first column so that end users do not see the column.

    UPDATE

    Found a very special way to do it in Power Query Editor. Just follow this below steps-

    1. Duplicate your base table and create a new table so that base value remains unchanged.
    2. Keep only Product column ensuring the order not changed. this will looks like below-

    enter image description here

    1. Now apply group by with an additional parameter as marked in red in the below image. you can see your expected output with these very simple steps.

    enter image description here

    Now you have a physical table and you can use this new value anywhere you need.