Running Sum # and Running Sum % based on status Power BI (DAX)

[Anonymous]

Hi,

I am struggling with creating Running Summ for value based on two statuses I have in my table. The problem is that I do not have dates, only text and numeric values (I created rank index as a help column). Please have a look at my data and screen below.

I need to calculate Running Sum for Column Distribution but for status "Gains" and "Gross" separately. So the Running Sum should be calculated for "Gains" and then starts again for "Gross".

Then I need to use that to create Percent of Total - also separately for "Gains" only and for "Gross" only... and then I need this Percent of Total again in another column in a Cumulative way for both Gains and Gross. This is very hard to do in DAX.

Can you please help me?? 

- I need to calculate: 

1. Running Sum (for distribution)

2. Percent of total separately for Gains and Gross (for distribution)

3. Cumulative Percentage separated for Gains and for Gross (for percentage calculated for distribution):

Is this possible to calculate via DAX? Can you help me as I am struggling with this for a long time and cannot find any answer neither on this Community formum nor anywere else.

Thank you!

Pawel

[parry2k · Accepted solution]

@Anonymous I added a status sort column in the power query, I think the value is 1 and 2 and then I sorted the Status column by this new Status sort column.

Since I cannot make changes in PQ in your new file, just add a Status Sort column from the original file I sent to you and sort the Status column by this new column. Good luck!

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

It worked, solved the sorting problem!

Thank you very much.

Regards

Pawel

[parry2k]

@Anonymous solution attached, tweak it as per your needs.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous yes, it is possible, share the sample pbix file, and will get back to you with the solution.

[Anonymous]

Hi,

Thank you very much for enclosed Power BI. All measures looks great, and they calculate as they should. However, I think I am still struggling with sorting problem. The sorting is key thing when it comes to Running Sum. Somehow my Index does not sort the way it does in your example.

Maybe this is because of the data set based on excel file? I followed all your instructions and get different output. Can you have a look at enclosed file? This is what I get:

https://1drv.ms/u/s!AtEHWSVFE9URi1k6vdb9LHZaSMiA?e=U1W748

As you can see Index does not sort as I need. What do I do wrong?

The whole Idea is to use both Statuses, and use Filter so I can see results either for Gains, or for Gross. Never for both in the same time. So when I use filter, index works fine, but calculations are still calculating whole column, not only selected status. 

I  even created status sort columns as You did (but you are did not use it anywhere). Can you please help me with the enclosed file?

Thank you in advance!

Pawel

[Anonymous]

Hi,

Thank you so much. I created an excel plus Power BI example. As I am new on this Community forum, I cannot enclose pbix files. I will share it via OneDrive, can you check this link?

https://1drv.ms/u/s!AtEHWSVFE9URi1dzhQ_h2MwQw5_D?e=gffwoA

Hope this works, please let me know. 

I keep there two index columns. One is only for sorting purpose as for some reason Power BI do not want to sort my Score Range in a visualisation the way I would expect (100-125 is first, 1-100 as second...). Second General Index I was trying to use for Running Sum purpose.. without the success 😞

I tried really a lot of examples available on this forum, but none was OK.

Thanks,

Pawel