Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Fixed denominator

I'm struggling with an issue and would love some assistance... I'm sure it's simple to solve, but I just can't get it.

 

I have a table with the following columns:

  • Products = the dimension of the table 
  • Receipts= number of receipts on which the product appears
  • Product= amount sold of each product

 

What I want to calculate are the products per receipt (green column), the calculation would be:

  • Products per receipt = Total product / TOTAL receipts
  • TOTAL receipts = 185 (distinct receipt ID) which does not correspond to the sum of receipts column, but to another sum, which comes from another table and is not at the Product level.

But by default, it makes the division at the row level, for example:

  • Products per receipt Prod 1 = 17/12 = 1.4
  • Products per receipt Prod 2 = 21/16 = 1.3
    These are the results shown in the red column.

But what I want are the results of the green column:

  • Products per receipt Prod 1 = 17/185 = 0.09
  • Products per receipt Prod 2 = 21/185 = 0.11

What I want is to fix the denominator 185, which is the total sum of distinct receipts

 

How can I do this?

Thanks in advance for any help here.

Need Help

1 Reply

  • lukiz84's avatar
    lukiz84
    Memorable Member
    4 years ago

    Hi,

     

    try

     

    ProductsPerReceipt =

VAR totalReceipts = 
  CALCULATE(
     SUM(yourTable[Total receipts]),
     ALL(yourTable)
  )

RETURN 
   DIVIDE (SUM([Total Receipts]), totalReceipts)