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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kivanov
Frequent Visitor

cumulative/running total percentage

Hi,

 

I am looking to find a way to get the running total/cumulative percentage of my data without a date as reference. I basically just want to add the previous row % to the next row (I attached how I want it to look, but it was done in excel). The "count" column is a calculated measure which counts the number of instances each error appeared, same for the % of grand total column.

Any help would be greatly appreciated! pbi issue.pngraw data.png

 

1 ACCEPTED SOLUTION

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
topangumanti
New Member

  • 16597164223823389273731641921023.jpg

     please help me, how to count % of each days / total..

Hi,

Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Fred_Bai
Advocate I
Advocate I

May I  know how to do this cumulative calculation in query editor with M language instead of DAX? 

Hi,

Why do you want to perform the cumulative calculation in the Query Editor and not in DAX?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ha, beacause I don't know how to play DAX currently. But I do need to built that report in Power BI before I skill up.

 

I can help with a DAX solution.  Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry, that is commerical confidencial data. Cannot share. But thank you.

Another reason is that, even you can solve it for me, I still cannot make any change basing on your DAX codes as I don't know how yet.

Beyond creating a single page visualization, I need that result to be used as slicer, filter or something else on other pages. I do believe you can use DAX to make that happen. However, if I can't handle it, I won't use it for now.

 

That's why I was asking if M.Function can do that or not? If yes, I prefer to learn it first. If no, will try another way. Rather than somebody build it and pass me the finish good only.

TomMartens
Super User
Super User

Hey,

 

I added something to your existing data model, I added a tabel that just contains the unique Error Types, this table alos contains a columns that I use to sort the Error Types.

 

Here you will a pbix file and an Excel file that contains sample data

 

I used three measures to create the following table

Sorting Categories.png

 

Here are the measures

This one just counts

cnt ErrorType = 
COUNT(ErrorTypeFact[ErrorType])

This calculates the contribution of one Error Type

ErrorType Contribution = 
[cnt ErrorType]
/
CALCULATE(
    COUNT(ErrorTypeFact[ErrorType])
    ,ALL(ErrorTypeFact)
)

This one sums the individual contributions of each Error Type in correspondence with the SortOrder of the Error Type

Error Type **bleep** Contribution = 
var errortypesortorder = MAX('ErrorType'[SortOrder])
return 
SUMX(
    ADDCOLUMNS(
        FILTER(
            ALL(ErrorType[ErrorType],ErrorType[SortOrder])
            ,'ErrorType'[SortOrder] <= errortypesortorder
        )
        ,"This", [ErrorType Contribution]
    )
    ,[This]
)

Hopefully this is what you are looking for

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Very nice, Tom! Love it! I did forget to add that I wanted it to be sorted by the count of errortype and have the cumulative contribution column sort dynamically by that. I don't know if that is possible or not. Additonally I linking the pbix I was working with (trial 1).

try 2.png

 

Ashish_Mathur
Super User
Super User

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The pbix file is located here

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hey,

 

thanks for the clarification and the pbix.

 

I will have a closer look over the upcoming days, but this will take a while due to the holidays.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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