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 measure calculating percentage difference 100% for overall count for all states or stateid.

DIVIDE ( [prev cnt], [curr cnt] )

But I need percentage as below:

example:

For state 'GA' and Stateid  there are four records you see below, I need to calculate 100% for those four records (GA: 4 records by 100%) 

Then next state 'CA' which has three records for that next 100% has to divide for those three records. 

Same goes for next states.

 

I need DAX to calculate 'previous cnt' and 'current cnt' column percentage difference individually for each state.

 

 

 

Thanks,

 

  • 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!

4 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper 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!

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Saideep16 

    The description is not that clear. It would save a lot of effort if you could:

    1) fill out the expected result column

    2) indicate which headers are column and which are measures. 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Saideep16 

    please try

    PERCENTAGE =
    DIVIDE (
    'Table'[DIFF CNT],
    CALCULATE ( SUM ( 'Table'[DIFF CNT] ), ALLEXCEPT ( 'Table', 'Table'[STATE] ) )
    )