Forum Discussion

Saideep16's avatar
Saideep16
New Member
3 years ago
Solved

Calculate Percentage difference between two columns and grouping on state

Hi Everyone,   Thanks in advance for all who tried to help me!!   I am looking for a measure to calculate percentage difference between two columns (Prev cnt, Curr cnt).   FYI, currently my mea...
  • sevenhills's avatar
    sevenhills
    3 years ago

    The output you are expecting for each row is not clear. Also, you did not mention whether DIFF CNT is a measure or a column!.

     

    Assuming, DIFF CNT is a column, let us say the TableState data is below:

     

    Percentage measure:

     

    PERCENTAGE = 
VAR _currState = SELECTEDVALUE(TableState[STATE])
var _prev = sumx(TableState, TableState[PREV CNT])
var _curr = sumx(TableState, TableState[CURR CNT])
var _curr1 = CALCULATE( sum(TableState[CURR CNT]), FILTER(all(TableState), TableState[STATE] = _currState))
var _prev1 = CALCULATE( sum(TableState[PREV CNT]), FILTER(all(TableState), TableState[STATE] = _currState))

return if (HASONEFILTER(TableState[STATE]), (_prev / _curr1) ,  (_prev / _curr ) )

     

     

     Output:

     

    Adjust the formula to your needs!

sevenhills's avatar
sevenhills
Super User
3 years ago

The output you are expecting for each row is not clear. Also, you did not mention whether DIFF CNT is a measure or a column!.

 

Assuming, DIFF CNT is a column, let us say the TableState data is below:

 

Percentage measure:

 

PERCENTAGE = 
VAR _currState = SELECTEDVALUE(TableState[STATE])
var _prev = sumx(TableState, TableState[PREV CNT])
var _curr = sumx(TableState, TableState[CURR CNT])
var _curr1 = CALCULATE( sum(TableState[CURR CNT]), FILTER(all(TableState), TableState[STATE] = _currState))
var _prev1 = CALCULATE( sum(TableState[PREV CNT]), FILTER(all(TableState), TableState[STATE] = _currState))

return if (HASONEFILTER(TableState[STATE]), (_prev / _curr1) ,  (_prev / _curr ) )

 

 

 Output:

 

Adjust the formula to your needs!