Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to add a calculation in a pivot table, dividing the sales amount for a certain product with the total number of orders for that certain date.
Order:
OrderCode | OrderDate | OrderCtr |
123 | 2020-07-29 | 1 |
124 | 2020-07-29 | 1 |
125 | 2020-07-29 | 1 |
Product:
OrderCode | ProductCategory | ProductName | ProductPrice |
123 | Toys | Ball | 45 |
123 | Books | Pippi | 12 |
123 | Toys | Doll | 32 |
I have the OrderDate as rows and the the ProductName as columns, but only withProductCategory = Toys like this:if (ProductCategory = 'Toys', ProductName)
In the measures I'm trying to do this calculation, but I'm not getting the total number of orders back:
SUM(ProductPrice) / SUM(TOTAL <OrderDate> OrderCtr)
Any help is appreciated!
try below then
=sum(ProductPrice)/sum(total <OrderDate>aggr(sum(OrderCtr),OrderDate))
If you need the total number of order per day disregarding the ordercode it should be
SUM(ProductPrice) / SUM(TOTAL {<OrderCode=>} OrderCtr)
or do I misunderstand your query?
Thanks Almen for trying to help out.
That didn't work. I need to be able to divide the SUM(Sales) with the total amount of orders that date, independently if the order contains any products in the selected category (toys).
So for every category per day you want Sum(Sales)/Quantity?
what is the expected outout?
I want to divide the total sales for every product, with the total number of orders for that day, independently if that order contains the product or not, i.e. for 2020-07-29 I want to divide with 3 as there are 3 orders that day.
Thanks in advance
this below expression should work.
SUM(ProductPrice) / SUM(TOTAL <OrderDate> OrderCtr)
Would you be able to share what you are trying to do? Which chart you are creating? What are the dimensions ? What is the expected numbers?
It doesn't work in pivot because of the if statement [if (ProductCategory = 'Toys', ProductName)]in your dimension. It reduces the total number of orders because books is not part of the dimension. Put ProductCategory as Dimension 1 and ProductName as Dimension2 and the expression
SUM(ProductPrice) / SUM(TOTAL <OrderDate> OrderCtr) will work.
try below then
=sum(ProductPrice)/sum(total <OrderDate>aggr(sum(OrderCtr),OrderDate))
Works great Kush, many thanks!