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: ...
Tips and Tricks
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!