cumulative/running total percentage

[kivanov]

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! 

[topangumanti]

• 

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

[Ashish_Mathur]

Hi,

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

[Fred_Bai]

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

[Ashish_Mathur]

Hi,

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

[Fred_Bai]

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.

[Ashish_Mathur]

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

[Fred_Bai]

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]

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

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

[kivanov]

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

[Ashish_Mathur]

Hi,

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

[kivanov]

The pbix file is located here

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[TomMartens]

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