Forum Discussion

JMSNYC's avatar
JMSNYC
Helper III
5 years ago
Solved

Sales Ratios Display

Hi.   Could you please help with the following problem: I have a FACT table from which I derived a sales ratio measure : Sales / Customers   Sales Ratio = divide('FACT'[Sales],DISTINCTCOUNT('FACT...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi JMSNYC 

    For your requirement I would use the below formula to calculate by each country.

    SR  = CACLULATE(divide('FACT'[Sales],DISTINCTCOUNT('FACT'[Customer ID]),ALLEXCEPT('FACT','FACT'[Customer ID]))

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

JMSNYC 

 

I'm not sure I'm following you. Your measure will divide the sales for a given country in a given month by the number of customers in that country in that month, which sounds correct. The total is just the total sales by month divided by the customers that month.

So what do you need to calculate? Can you provide. Depiction of the expected result?

JMSNYC's avatar
JMSNYC
Helper III
5 years ago

Hi Paul.

 

Thank you for your quick follow-up question. Here is an example

I would like the measure to compute numbers the way it is described in the Green Table, while today, as per the example, the computing follows the red sample.

Is it more clear? Thank you again for your support.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    JMSNYC 

    Again, I'm not sure what is going on at your end. Here is a dummy dataset:

     

    If I want to see the number of items divided by the number of customers for each country  by month, the measures are straightforward:

    1) Number Items = SUM(FactTable[Items])

    2) Number of Customers = DISTINCTCOUNT(FactTable[Customer ID])

    3) Items by Customer = DIVIDE([Number Items], [Number of Customers])

     

    and you get this: