Visual calculation: running sum - sorting issue

[BenediktW]

Hello together,

I ve tried to utilize the new visual calculations to calculate running sums (cumulative percentage sales per customers as running totals).

Anyhow, I have the problem that the running sum gets calculated based on the alphabetical order of the category. Not on the sales volume (how I want it to be), on which the table is sorted. Is there any solution for this?

Thank you very much!

On the "Anteil_am_Gesamtumsatz" you can see the correct order. Anyhow the running sum is not calculating based on the sorted order but on the alphabetical order of my category. 

[minneapolispbix]

Is there a way to have the visual calculation respond to the sort order for any column of the table? 

Say I have a percent and cumulative percent, and I want that running tally to add up based on when I sort by column A or column B. Like it will dynamically respond to different sorting.

[jeroenterheerdt]

no, right now, just like with a window function you dicate the exact sort order. We have not gotten around to making this responsive to however the visual is sorted.

[BenediktW · Accepted solution]

@lbendlin 

Finally I found a solution without runningsum but with visual calc, which is working properly:

FORMAT(SUMX( WINDOW(1, ABS, 0 ,REL, ALLSELECTED([Customer, ],  [Sales]), ORDERBY([Sales], DESC

)), [Share total Sales]) , "Percent")

[jeroenterheerdt]

you can now do this with just runningsum (and without format): RUNNINGSUM([Sales], ORDERBY([Sales], DESC)). Then format the visual calculation using the data formatting option.

[AnneKeli]

FINALLY!! THANK YOU!!! I have been searching for HOURS for this solution. I cannot believe it's this complex to change the sort order. THANK YOU, THANK YOU, THANK YOU

[jeroenterheerdt]

We want to change this and are working through options here. What would you like to have seen?

[AnneKeli]

I acree with @Lajouac - an ORDERBY parameter would be what I expected.

[Lajouac]

Hi @jeroenterheerdt !
I am very happy to have found this solution !
I would have imagined an ORDERBY parameter inside the running sum function, in able to choose the column to use to order the running sum.
Thanks for working on this !

[jeroenterheerdt]

cool, because that's exactly what I was thinking 🙂

[lbendlin]

please show the visual calculation formula

[BenediktW]

That is the formular for running sum

My visual is sorted like this (based on Sales): 

However, as soon as I enter the visual calculation area, it automatically ignores the sorting of the table and just takes the alphabetical order of my category (Customer): 

[lbendlin]

confirmed as a bug

@jeroenterheerdt 

[jeroenterheerdt]

it's not a bug, it's how it was implemented right now. What do you expect? Do you want the visual matrix to _always_ be sorted on whatever is the visual?

[lbendlin]

it's not a bug, it's how it was implemented 

that means the implementation was buggy 🙂

[jeroenterheerdt]

no, that was intentional. It follows the model sort order, such a sorting columns. Just blindly following the sort order of the visual is not the only choice.

[darrenfishell]

Unless specified otherwise with PARTITIONBY or ORDERBY, yes! 

[jeroenterheerdt]

thanks for letting me know. What about the sort order from the model? And how about functions that don't have the ORDERBY and PARTITIONBY parameter, like RUNNINGSUM?

[BenediktW]

Thank you for the feedback, in my case: I cannot take simply any sorting order from the model as the sorting order must be based on a measure here. Thats why I currently see no way how to use the runningsum calculation for my advantage. 

When a "visual" calculation is ignoring the set-up of my visual, but is taking randomly alphabetical order of the category (my model is not sorted alphabetically) I dont really see the point.

This is also declared as a issue in various reviews (e.g. : DAX Fridays #225: RUNNINGSUM | Visual calculation functions - YouTube)   Minute 2:30)) 

In case I missed it: is there any option influence the runningsum sort-oder (besides query editor)?

[jeroenterheerdt]

there is not yet, we are actively thinking through what this should look like. Please feel free to give your input on this form: https://forms.office.com/Pages/ResponsePage.aspx?id=v4j5cvGGr0GRqy180BHbR-XFZhNDe99FoNSD_SJIDNlUNkNR...