cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Calculating continuation % from the previous row for records in an unpivoted matrix

Hi All,

Having nearly broken my brain trying various ways to calculate the 'subject', I am hoping someone can point me in the right directin as to how I would go about calculating the continuation % as a measure, in a matrix, based on records that denote an online session funnel as per the below:

In effect I need to calculate the %s based on the filter context/record level detail.

Tips and ricks welcome as not wanting to leanr not simply get the answer! although I will be grateful either way!

4 REPLIES 4
Super User

Can you please explain what the Contunuation % column is? It a percentage sure. Is Total Sessions the Numerator? If so, what the denominator? Is it the same for all rows? Why is 100,000 100% but 500,000 only 50%? It is 5 times as big, should it not be 500%

Sorry, but the data does not make sense.

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Frequent Visitor

Good evening and thank you for offering up some help. Forgive my poor grammar when asking the question, I meant I am wanting to learn how rather than be given the answer so I created that dummy table in excel. Continuation basically looks at the % of the new value / the previous value, as in the % of customers who continue through to funnel towards a purchase event. So I am trying to create a calculation that wilenable me to reflect this in a matrix where the records represent the funnel. I could not share my actual pbix file.

Super User

In looking at your data, I suspect that the first value should be 1,000,000. Then every subsequent Continuation % is the percentage of the previous row. If that is the case, then you should look into Visual Calculations:

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

In the below screen shot, I have used Visual Calculations on a simple table. The first calculation added was [Previous Sessions] = PREVIOUS ( [Total Sessions] ). Next I added [Continuation %] = DIVIDE([Total Sessions], [Previous Sessions], 0). Unfortunately you cannot format these measures so I had to add " * 100 " to make it a percentage number. Lastly, I hid the [Previous Sessions] calculation from the table visual above.

I leave it to you, dear reader, to take this last calculation and cast it to a string, strip out the trailing decimal zeros, and add a % sign.

Best of luck.

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Frequent Visitor

This looks annoyingly simple considering I had been writing variables longer than my arm that were failing! but is it easy when you know how so I applaud and thank you!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.