Running total sorted by calculated measure and related columns

[vstefano]

Good evening, I have a visual table as below: opp. number and a calculated measure (Value €) that sums the amount of each opportunity (opp.number can be present in multiple rows of imported data).

I want to calculate three columns like below: 1. running total SORTED by Value € (decreasing order), 2.% running total, 3. a value of 1 up to first row of "% running total" that is >=80% (ie I want to be able to count the number of opportunities that generates 80% of my amount.

Any suggestion would be highly apreaciated.

Thanks in advance,

Stefano

[vstefano · Accepted solution]

Problem solved using a calculated formula that takes into account two conditions, a threshold and an index to order records with identical amount. I did not use visual calculation as I needed a card, a unique number, that display me % of grouped records that generates 80% of my volume. 

[ToddChitt]

Totally agree. 

Follow this thread Re: Share your thoughts on visual calculations (pr... - Page 8 - Microsoft Fabric Community In there is a comment I made (just yesterday and in regard to your issue 🙂 ) about sorting of rows for a Running Sum Visual Calculation. Feel free to find it and comment on your frustration with the bug. The more comments the more Microsoft is likely to listen.

Best of luck.

[vstefano · Accepted solution]

Problem solved using a calculated formula that takes into account two conditions, a threshold and an index to order records with identical amount. I did not use visual calculation as I needed a card, a unique number, that display me % of grouped records that generates 80% of my volume. 

[Anonymous · Accepted solution]

Hi @vstefano ,

Really glad to hear that your problem has been solved. If possible, please accept the reply as solution. More people will benefit from it, thanks.

Best Regards,

Stephen Tao

[ToddChitt]

Hello @vstefano 

I suggest you look into Visual Calculations, which is a Preview Feature (must be enabled, then restart Power BI). I'm trying to work up an examply to help you but you should investigate them yourself as well. There is a Template for Running Sum that may help you here.

[vstefano]

Hi Tod,

thanks for the answer. I had already tried to use visual calculation, but the calculation is authomatically sort by opp.name not by Value €. I am not very familiar with this.

A workaround that I found at the moment is to insert a new axis, with the total of the opportunity. In this way the new axis (forst column) overcome the order of "opp.number" and the running total is calculated correctly.

The problem with visual calculation is that the table is see as a sort of graph, hence if I sort by value € for example, the running total refers always to the order of the first column:

It would be good that visual calculation adpated to the actual sorting of the table.

Stefano