Sales Ratios Display

[JMSNYC]

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

[v-deddai1-msft]

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

[pranit828 · Accepted solution]

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]

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

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]

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