DAX Measure - Add sum of unique invoices and categorize

[Anonymous]

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

[amitchandak · Accepted solution]

@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