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'[Customer ID]), that I display in a Pivot Table :

  • Column 1 is yearmonth
  • Last Column ("Total") is Sales Ratio

In the table below, SR=88% in Jan, 124% in. Dec etc ...

 

 

Issue comes when I want to show the Sales Ratio for each individual country ... it divides the 88% across countries, while I need the individual SR by yearmonth and by country.

 

Could anyone help me write the proper Measure ?

 

Thank you in advance

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]))

5 Replies

  • 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:

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    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]))

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi JMSNYC ,

     

    You can use the following measure:

     

    measure1 =
DIVIDE ( SUM ( 'FACT'[Sales] ), DISTINCTCOUNT ( 'FACT'[Customer ID] ) )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai