Counting number of invoices in a sales report

[LBala]

Hi all;

I have a sales report and I need to count the number of invoices showing:
- Less than 1 piece being sold
- Only 1 piece being sold
- 2 pieces being sold
- 3 pieces being sold
- More than 3 pieces being sold

I am trying to get those results being able to filter:
- By state
- By period (year, month and week day)
- By department

Here is a picture of the diagram view:

Here is a picture of the pivot table with the filters and results:

In the picture above, for the filters applied I have a total of 6 invoices:
- 5 invoices showing only 1 piece sold (invoices 3931, 3932, 3933, 3942 and 3943, "QTY_Sold" = 1)
- 1 invoice showing 5 pieces sold (invoice number 3937, 3 items listed, "QTY_Sold" = 2 + 2 + 1)

I would like to get the results as shown in the formats A and B below:

Format A) By number of invoices:

Format B) By number of invoices as a percentage from the total:

I have uploaded the excel file I am working with on to dropbox for easy download (file size: 343 KB):
https://www.dropbox.com/s/0fq6ieepz16sny4/Report_Sample.xlsx?dl=0

I can't figure out a way to show the results as in the pictures above.
Any help or suggestions will be appreciated.
Thanks.

 

[Matt Allington]

The pattern you need is this

=CALCULATE(
     DISTINCTCOUNT(Invoices[Invoice_Num]),
     FILTER(
          values(Invoices[Invoice_Num]),
          CALCULATE(sum(Invoices[QTY_Sold]))=1
     )
)

Just change the =1 to be what ever you need.

 

[ImkeF]

Bucketing is described here: https://blogs.msdn.microsoft.com/analysisservices/2014/06/05/bucketing-values-in-dax/

 

[LBala]

Matt, that is exactly what I needed! It works perfectly.


ImkeF, thanks for the article about bucketing, that’s interesting.


Thank you guys.