Forum Discussion

ABmagic's avatar
ABmagic
Frequent Visitor
8 years ago

% from previous step

Hello, I'm looking for a way to calculate % from previous step. My table has data for each week. Here's an example of how it is structured for any given week:


 

 

 

 

 

 

 

 

 

I'm looking for some DAX formulas that can help me calculate the yellow highlighted cells.

 

I've looked at some other postings, but had no luck so far.

 

 

Thanks!

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ABmagic,

     

    Try out these formulas please.

    % of max =
IF (
    [Index] = 0,
    [Visits] / [Visits],
    [Visits]
        / CALCULATE ( SUM ( Table1[Visits] ), FILTER ( Table1, Table1[Index] = 0 ) )
)
    % of preious step =
VAR current_index = [Index]
VAR pre =
    CALCULATE (
        SUM ( Table1[% of max] ),
        FILTER ( 'Table1', 'Table1'[Index] = current_index - 1 )
    )
RETURN
    IF ( ISBLANK ( pre ), BLANK (), [% of max] / pre )

    from

     

    Best Regards,

    Dale

     

    • ABmagic's avatar
      ABmagic
      Frequent Visitor
      8 years ago

      With my initial stab at it % from previous, I had some trouble. I was able to get "% of max" to work using a different method:

       

      Measures:

       

      Step Value = CALCULATE(SUM('Conversion Funnel'[Visits]))

      Sum of All Visits =
      CALCULATE(
      SUM('Conversion Funnel'[Visits]),
      'Conversion Funnel'[Steps] IN { "All Visits" }
      )

      % of All Visits = Divide([Step Value],[Sum of All Visits])