Calculate Percentage difference between two columns and grouping on state

[Saideep16]

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 · Accepted solution]

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!

[tamerj1]

Hi @Saideep16 

please try

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

[FreemanZ]

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. 

[amitchandak]

@Saideep16 , refer if offset can help

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U