Forum Discussion
Average SKU per Invoice calculation
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
Hi
Anonymousyou 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> )
7 Replies
Hi
Anonymousyou 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> )
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?
Hi Anonymous
how are you performing your multiplication>?
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
