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

6 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Anonymous Sample data and expected output will be really helpful to understand it better and also to solve the issue quicker.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community 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] )

       

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    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
      Community Support
      7 years ago

      Hi Anonymous,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.


      Regards,
      Frank

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there,

     

    I want to show % differences between my control and tests.

     

    Imagine a table like this:

     

    Control      25

     

    Test A        30

     

    Test B        18

     

    I want to know as another column what Test A and then Test B are as a percentage of Control. How can I do this?

     

    Thanks