Average SKU per Invoice calculation

[Anonymous]

Hello All,

I need help calculating the average SKU per invoice, the calculation of which is=

(SKU Sold Per Invoice X No of Invoices)/Total No of Invoices

I have a transaction table with invoice numbers and their corresponding SKU's, from which i calculated the no of SKU's per invoice which is shown in the following table: 

Invoice No	SKU Count
A1001	22
A1002	23
A1003	23
A1004	24
A1005	25
A1006	26
A1007	26
A1008	26
A1009	27
A1010	28
A1011	28

Now, what I'am struggling with is the no. of invoices per SKU count, for eg:

23 SKU's have been billed twice and 26 SKU's have been billed thrice.

How can I calculate the no of invoices per SKU count?

Please help

[v-cherch-msft]

Hi @Anonymous

You may use SUMMARIZE Function to get a new table. Then you may get the no of invoices per SKU count with the table. For example:

Table =
SUMMARIZE ( Table, Table[Invoice No], "SKU Count", [Meaure] )

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft,

I tried the method mentioned but I'am facing an error.

[LivioLanzo · Accepted solution]

Hi 
@Anonymous

you need to create a calculated table which returns the table you're showing below. Then drop the SKU Count on the rows section of a matrix and add a measure which does COUNTROWS( <the_calculated table_you_created> )

[Anonymous]

Hey @LivioLanzo,

Thanks for your help.

I've been able to do the required calculation.

[Anonymous]

Thanks @LivioLanzo,

I created a table using:

Table = SUMMARIZE('Transaction','Transaction'[Inv No.],"Count items",COUNT('Transaction'[ITEM]))

Now, I created a measure:

Count=COUNTROWS(Table)

When i multiply them in a measure, Count items * count, I obtain the following:

Count items	Count	Count items*Count
22	1	22
20	1	20
19	5	475
18	2	72

There is a duplicacy taking place at the time of multiplication.

How do i remove that?

[LivioLanzo]

Hi @Anonymous

how are you performing your multiplication>?

[Anonymous]

@LivioLanzo, I created a measure:

Measure = SUM('Table'[Count items])*[Count]