Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ThomasSan
Helper IV
Helper IV

Cumulative Totals from largest to lowest and without date

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

ThomasSan_0-1633961773847.png

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:

ThomasSan_1-1633962984563.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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
)
)
 

 

Picture1.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

9 REPLIES 9
Jihwan_Kim
Super User
Super User

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
)
)
 

 

Picture1.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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. 

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

 

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 

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

ParametersDefect 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

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.

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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. 

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.