Percentage Calculation

[SachinC]

Hi,
I want a new calculated field so that it can calculate Percentage based on two fields by Route number.
 
E.g.

My datagrid currently contains 3 columns

Field 1: Route from Table A
Field 2: Drops from Table B
Field 3: Returns (which is Count of Customer) from Table A

This currently looks like this:-

Route   TotalDrops    Returns
1       100    5
2       100  10
3 50  25
4 10  5

I want the new field for PercentageReturns to be grouped by Route, so that it calculates the Percentage based on Returns and TotalDrops by Route.

E.g.

Route   TotalDrops    Returns PercReturns
1       100    5 5
2       100  10 10
3 50  25 50
4 10  5 50

How do I do this please?

Thanks

[sturlaws]

Hi @SachinC ,

your specifications is a bit short when it comes to what your desired outcome is. Here is the code for creating a calculated column:

PercentageReturns =
VAR _route =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Route] ) )
VAR _drops =
    CALCULATE (
        SUM ( 'Table'[TotalDrops] );
        FILTER ( ALL ( 'Table' ); 'Table'[Route] = _route )
    )
VAR _returns =
    CALCULATE (
        SUM ( 'Table'[Returns] );
        FILTER ( ALL ( 'Table' ); 'Table'[Route] = _route )
    )
RETURN
    DIVIDE ( _returns; _drops )

keep in mind that when you aggregate like this in a calculated column, the value is repeated for each route, and if you later on sum over this column, it will be wrong.

[SachinC]

For some reason that didn't work. Bear in mind Returns is a Count not Sum.

is there a ";" missing? It's suggesting it is.

Thanks!