cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ivan274
Frequent Visitor

Subtotal Column - Empty rows for other group rows

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 individualDate of purchase Product GroupGroup Total
Homestreet 11-1-2021A1
Homestreet 11-1-2021B 
Homestreet 14-2-2021A1
Teststreet 21-2-2021A1
Teststreet 21-2-2021C 
Teststreet 31-2-2021C1

 

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

 

Best regards and thanks in advance,

 

Ivan Pettinga

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
Ivan274
Frequent Visitor

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

v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!