cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors