Cumulative Totals from largest to lowest and without date

[ThomasSan]

Hi everyone, 

I am a bit surprised that my use case seems to be somewhat exotic even though I regularly see it in business but I would simply like to create a running total of values (here: gross margin) which are ordered from largest to smalles. It should look like as follows

It is absolutely easy to implement using Excel where you just use the formula "=if(isnumber(C1);C1+B2;B2)". But why is there nothing equally simple in PowerBI?

I have tried several tutorials (such as https://www.youtube.com/watch?v=f5k4fD1YJrQ&t=444s&ab_channel=EnterpriseDNA or https://community.powerbi.com/t5/Desktop/Running-Total-on-a-non-date-column/m-p/44470 or https://stackoverflow.com/questions/32032792/running-total-without-any-date-filters-in-dax) but none of them was helpful. Does anyone here know any adivce?

My data frame looks as follows where I have colored the relevant columns in green:

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Running total from the highest : =

VAR currentgrossmargin = [Gross Margin Measure :]

RETURN

CALCULATE (

[Gross Margin Measure :],

FILTER (

ALL ( Data[Product No] ),

[Gross Margin Measure :] >= currentgrossmargin

)

)

[Ansa4038]

Hi Jihwan,

Please reply asap if u can.

Unfortunately this doess not work when data set has two same values.

Let's say data is 11,5,5 then the cumulative total is shown as 11,20,20 instead of 11,15,20. how can this be corrected ?

Please help as this will help me in making an important file quickly. 

[Jihwan_Kim]

HI,

Please provide a sample pbix file's link with how does an expected outcome look like. And then I can try to look into it to come up with a more relevant solution for your sample.

Thank you.

[Ansa4038]

• https://drive.google.com/file/d/1sY5gEWgVSi6diKn47iSzdDuLHZj8njK_/view?usp=drivesdk 

Hi sorry I am new to power bi community. Please check the link with this comment . Designer wise Pareto comes correct. But Parameter wise Pareto doesn't come out well when defect count has same numbers. Eg 10 after another 10 

[Ansa4038]

In case the pbix file, does not work , here is the table for which i needed pareto

Parameters	Defect Count
correct electrical plan(34196)	11
electrical elevation(34177)	10
abels and Notes.(34212)	10
PLACARD DIRECTORY (34215)	9
correct notes for city.(34170)	8
interconnection(34209)	8
Drafting, Vent, ETC(34179)	7
Module Placement as per Client Layout(34168)	5
Dimensions of Property Line (34175)	5
Attachment Placement & Count of Attachments(34184)	4
GENERAL Notes(34192)	4
Electrical Equipment Schedule(34201)	4
Correct Wire Size After Inv.(34203)	4
Correct Wire Size.(34211)	3
LD Match page no.5(34214)	3
Check Leader and Text.(34218)	3
Check Fire Setback and Ridge Line(34181)	2
Check TOTAL ARRAY AREA and Roof Area %(34188)	2
Check MOUNTING DETAILs as par Roof (TILE Roof and Compo.)(34194)	2
Check MOUNTING DETAILs PORTRAIT and landscape as per layout(34195)	2
Check Correct. inverter Size(34207)	2
Check North arrow(34216)	2
File Naming Convention (Last Name, First Name)(34164)	1
Check properly as per Client Prop. Information(34167)	1
Verify the Meter and Main Panel Location(34172)	1
check vent size as per given in template.(34176)	1
Use correct notes for city.(34191)	1
Match the Callout of Line Diagram with the Electrical Equipment Schedule Callouts(34208)	1

[Jihwan_Kim]

Hi,

Thank you for your message.

Could you please check the attached file whether it suits your requirement?

I am not sure how to order parameters if it has the same defect count number, but I tried to order those by the number that is inside (  ) in each row.

[Ansa4038]

Hi Jihwan, 

Thanka lot for the solution. It does work with this data. But some other data sets do not have parameters numbered. In that case, it wont work right ? Are there some other techniques to get this done when parameters are only sentences ? Can we create a pareto with rank technique ? (Here too, will it be possible to give unique rank to defects with same values ? Your help is much appreciated. 

[Jihwan_Kim]

Hi,

Thank you for your feedback.

May I ask how do you rank the same number differently? Is it ranked by alphabetical order? Or is there any other category that can be referenced?

I suggest use that other column to assign different ranking number or use the Parameters column to rank by alphabetical order, and then I believe you can treat the same number in a different manner.

Thanks.

[ThomasSan]

Hi Jihwan,

thank you very much for your quick reply. Your solution has finally produced the outcome that I was looking for! Thanks a lot!