topic Re: Calculate Percentage difference between two columns and grouping on state in DAX Commands and Tips

Calculate Percentage difference between two columns and grouping on state

Saideep16 — Tue, 27 Dec 2022 15:44:44 GMT

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,

Re: Calculate Percentage difference between two columns and grouping on state

amitchandak — Tue, 27 Dec 2022 11:56:51 GMT

Saideep16 , refer if offset can help

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

Re: Calculate Percentage difference between two columns and grouping on state

FreemanZ — Tue, 27 Dec 2022 15:17:16 GMT

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.

Re: Calculate Percentage difference between two columns and grouping on state

tamerj1 — Tue, 27 Dec 2022 16:18:00 GMT

Hi Saideep16

please try

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

Re: Calculate Percentage difference between two columns and grouping on state

sevenhills — Tue, 27 Dec 2022 23:40:10 GMT

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!