Subtotal Column - Empty rows for other group rows

[Ivan274]

Hey everbody,

I would like to create a column that shows the occurance of a purchase on a specific adress and date. In other words, i would like to perform a distinct count of a combined Adress & date in the column Group Total. 

Usually, i would use a DISTINCT COUNT function for the combination of a DATE & Adress combined with an ALLEXCEPT statement for this combination. However, in this specific case I require the other columns to be empty. 

Adress of individual	Date of purchase	Product Group	Group Total
Homestreet 1	1-1-2021	A	1
Homestreet 1	1-1-2021	B
Homestreet 1	4-2-2021	A	1
Teststreet 2	1-2-2021	A	1
Teststreet 2	1-2-2021	C
Teststreet 3	1-2-2021	C	1

Is there anyway to perform this action in Powerbi using DAX or Powerquery?

Best regards and thanks in advance,

Ivan Pettinga

[Ivan274]

Thank you for your answer, i managed to get it working based on your feedback! 

[v-jayw-msft · Accepted solution]

Hi @Ivan274 ,

You could add an index column. Use MAX() and ALLEXCEPT() to get the max index for each group. Then if index equal to max index, then group total, else blank.

Best Regards,

Jay