How to get the sales amount for each customer

[1up]

Hi,

I'd like to get the sales amount for each customer, which will be used in a calculated column to get the sales ratio of each customer.

I have a measure 'Sales' which is including the sales to all customers. If used in a simple table visual, it is filtered to show the sales per customer:

Existing table 'Sales': (Many others exist but to simplify)

Customer   Sales

A                90

B                 7

C                 3

...

Wanted output, Calculated column 'Sales Ratio':

Customer   Sales Sales Ratio

A                90       0.90

B                 7        0.07

C                 3        0.03

...

I'd like to get the sales amount for each customer and divide it by the total sales, as each row is being evaluated. How do I write to pick up the sales amount for a single customer? (I want the expression to go through all customer names and evaluate each one seperately.)

Thanks for help,

Br,

Daniel

[Anonymous · Accepted solution]

Hi @1up ,

According to your statement, I think you want to create a calculated column for sales rate for each group/level.

You can refer to my sample and have a try.

Sales Ratio = 
DIVIDE('Table'[Sales],CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Region])))

Result is as below.

Best Regards,
Rico Zhou

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

[DOLEARY85]

Hi,

try this, it works as either a calculated column or measure: 

Ratio = DIVIDE(CALCULATE(SUM('Table'[Sales])),CALCULATE(SUM('Table'[Sales]),ALL('Table')))

Make sure you select the field in the data pane and select decimal number (2 places). Result:

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[1up]

Thank you Vicky for the reply, perhaps the example make it seem too simple. I would need to get the row-level sales amounts in a formula for further calculation. So I believe I need another solution than the percent of grand total built in functionality. Perhaps using Calculate and/or a Filter function would retrieve the row-level sales amounts based on customer?

[vicky_]

You can always use the built-in percentage of grand-total, even with measures. Right click on the measure, go to the bottom > Show Value as > Percent of Grand Total