Forum Discussion

SachinC's avatar
SachinC
Helper V
6 years ago

Percentage Calculation

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
Tips and Tricks

2 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    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's avatar
      SachinC
      Helper V
      6 years ago

      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!