Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

DAX Measure - Add sum of unique invoices and categorize

Dear community, for a Dax formula I need your help.


As a data source, i have an Excel file with sales from 2019.

"Column A" shows all invoice numbers, "Column B" shows the invoice amount, "Column C" shows the customer-ID.

 

Some customers have purchased multiple products through an invoice-number. As a result, invoice numbers occur several times with different amounts. Now I would like to add the sum of the unique invoices like: "if sum unique invoice <10 then "<10"; if >=10 & <20;" 10-19.99" and so on.

 

I would be happy if you could help me, thank you!

Daniel

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Bucketing/segmentation. You have created an independent table with buckets.

Min value, Max Value, Bucket name 

 

measure

Amount = sum(Table[Column C])

 

measure with bucket table

sumx(filter(values(Table[Column A]) , [Amount] >= Min(Bucket[Min Value]) && [Amount] <= max(Bucket[Max Value]) ), [Amount])

 

So this for all the measures you want with bucket table.

 

refer my video or blog on that

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Anonymous , Bucketing/segmentation. You have created an independent table with buckets.

Min value, Max Value, Bucket name 

 

measure

Amount = sum(Table[Column C])

 

measure with bucket table

sumx(filter(values(Table[Column A]) , [Amount] >= Min(Bucket[Min Value]) && [Amount] <= max(Bucket[Max Value]) ), [Amount])

 

So this for all the measures you want with bucket table.

 

refer my video or blog on that

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.