11-11-2016 08:54 AM
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.