Search code examples
powerbidaxcumulative-sum

Cumulative total percentage in Power BI

I have a summary table in Power BI which shows how many days it takes for leads to convert to a sale. It has 2 columns, sum_convert (the amount of days in between lead creation date and converted date) and count_lead (the count of leads that have taken that amount of days to convert), both are numeric values. Here is an example of the data:

enter image description here

What I want, is a column next to count_lead that shows the running percentage total in the specific ascending order of sum_convert. Currently I've created a measure called lead_count which is the sum of count_lead. Then I've attempted to create the cumulative total with the following measure:

Cum_Lead = calculate([lead_count], FILTER(ALL(Convert_Count_Summary[Sum_Convert]), SUM(Convert_Count_Summary[count_lead]) <= [lead_count]))

This creates a cumulative total, but not in the specific sum_convert order, it's in the order of largest volume for count_lead. Any idea what I need to change so that it's in the order of sum_convert?

Solution

  • You could do this in Power Query using M:

    = Table.AddColumn(#"Previous Step", "Cumulative_Count_PQ", each List.Sum(List.FirstN(#"Previous Step"[count_lead],_[sum_convert]+1)), type number)

    Or as a calculated column using DAX:

    Cumulative Count DAX = 
    CALCULATE ( 
        SUM ( Convert_Count_Summary[count_lead] ),
        ALL ( Convert_Count_Summary ),
        Convert_Count_Summary[sum_convert] <= EARLIER ( Convert_Count_Summary[sum_convert] )
    )

    Edit:

    Cumulative percentages in Power Query:

    = Table.AddColumn(#"Previous Step", "Cumulative_Count_Percent_PQ", each List.Sum(List.FirstN(#"Previous Step"[count_lead],_[sum_convert]+1)) / List.Sum(#"Previous Step"[count_lead]), Percentage.Type)

    Cumulative percentages calculated column in DAX:

    Cumulative Count % DAX = 
VAR _Numerator = 
    CALCULATE ( 
        SUM ( Convert_Count_Summary[count_lead] ),
        ALL ( Convert_Count_Summary ),
        Convert_Count_Summary[sum_convert] <= EARLIER ( Convert_Count_Summary[sum_convert] )
    )
VAR _Divisor = 
        SUM ( Convert_Count_Summary[count_lead] )
RETURN
    DIVIDE ( 
        _Numerator,
        _Divisor
    )