Reply
Highlighted
New Member
Posts: 1
Registered: ‎11-11-2016

Percentage of Prior Stage

Hi,

I'm having trouble writing the DAX expression I need to calculate the percentage of the previous stage similar to what is displayed in the pop-up on a funnel chart.

 

In my dataset, I have the following fields:
LeadId - need distinct count of these which determines the numerator & denominator of the percentages
Active Flag - this indicates if the record is the last (max) stage for a lead
Stage Sort - this is an integer that indicates at which stage the record is at
Stage Progression - this is just the verbage for the labels & is sorted by the Stage Sort field

 

I can easily get the denominator -- Count of Current Stage = CALCULATE(DISTINCTCOUNT('Conversion Velocity'[Lead ID]),'Conversion Velocity'[Stage Progression], 'Conversion Velocity'[Lead ID] <> "NULL",'Conversion Velocity'[Active Flag] = 1)

 

How do I get the numerator? In English, it's the distinct count of the LeadId's where the Stage Sort = Stage Sort -1 of the record where Active Flag = 1

 

Thanks in advance for any assistance.

 

Michele