Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Percentage Difference for a single row

Hi there,   I have a table that contains 3 rows.   Is there a way I can show rows 2 & 3 as percentage differences from row 1?   Thanks,   JAson
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    Create the measures as below.

     

    Measure = CALCULATE(SUM(Table1[t1]))
    rank = RANKX(ALL(Table1),[Measure])
    perc = var onerow = CALCULATE(SUM(Table1[t1]),FILTER(Table1,[rank]=3))
var restrow = CALCULATE(SUM(Table1[t1]),FILTER(Table1,[rank]<>3))
return 
restrow/onerow

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous,

 

I made one sample for your reference.

 

1. Insert an index column in Power query.

 

 

2, Create a measure as below.

 

perc = 
VAR row1 =
    CALCULATE ( SUM ( Table1[t1] ), FILTER ( Table1, Table1[Index] = 1 ) )
        + CALCULATE ( SUM ( Table1[t2] ), FILTER ( Table1, Table1[Index] = 1 ) )
        + CALCULATE ( SUM ( Table1[t3] ), FILTER ( Table1, Table1[Index] = 1 ) )
VAR restrow =
    CALCULATE ( SUM ( Table1[t1] ), FILTER ( Table1, Table1[Index] <> 1 ) )
        + CALCULATE ( SUM ( Table1[t2] ), FILTER ( Table1, Table1[Index] <> 1 ) )
        + CALCULATE ( SUM ( Table1[t3] ), FILTER ( Table1, Table1[Index] <> 1 ) )
RETURN
    row1 / restrow

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Anonymous's avatar
Anonymous
Not applicable
7 years ago

This is really useful and thanks so much.

 

The only issue I have here is that the values I'm trying to calculate differences from are already measures.

 

So as an example  (Table1[t1]) would be a calculated measure already.

 

Thanks,

 

JAson

CALCULATE ( SUM ( Table1[t1] )