Calculate cumulative percentage without date

[Reza]

Hi everyone,

I have searched this and tried so many combinations from various pages including here and other communities but none of them seems to fit with my requirements.

This is my model in Power Pivot:Power Pivot Model

 Sections Lookup looks like this:

Sections Lookup

The Sections looks like this:

Sections

The Section Count is a measure: 

Section Count:=SUM([Count])

The Percent calculated like this:

=DIVIDE([Count],SUM([Count]),0)

My Pivot Table look like this:

Pivot table with percent

The Section Count is the measure.

Now the problem.

I can create "% Running Total In" in Excel on the pivot table but this model is going to be used in Power BI desktop and there I don't have this option but % of Grand Total only. So I need to create this column on my model to look like this:

Require % Running Total Column

Please note that the Sections and Section Lookup are Views in my database and I just added Sections Lookup to the model for demonstration but don't need that in reality.

Any help would be extremely appreciated. 

Thanks

[MFelix]

If it's possible send it by private message it would be very helpfull.

Regards

MFelix

[MFelix]

Hi @Reza,

Since I was making the use of a single table to do the calculations within Power Pivot my results where different from your data model you should use this measure in your Power Pivot model:

% Running Total = 
DIVIDE (
    CALCULATE (
        [Section Count],
        FILTER (
            ALL ( 'Sections'[Section] ),
            'Sections'[Section] <= MAX ( 'Sections'[Section] )
        )
    ),
    CALCULATE ( [Section Count], ALL ( 'Sections'[Section] ) )
)

This gives the result below, however as I said best practice is to make this calculations inside the PBI and not upload it as part of your data model.

Regards,

MFelix

[Reza]

Hi @MFelix,

I am using the last formula you provided as a measure on PBI

% Running Total = 
DIVIDE (
    CALCULATE (
        [Section Count],
        FILTER (
            ALL ( 'Sections'[Section] ),
            'Sections'[Section] <= MAX ( 'Sections'[Section] )
        )
    ),
    CALCULATE ( [Section Count], ALL ( 'Sections'[Section] ) )
)

If I change the sort on the 'Section Count' (descending) then the % Running Total does not work correctly (screenshot from my previous post)

Regards,

Reza

[Reza]

Hi @MFelix,

I am still looking to find a solution for this problem but cannot get any answer. I am surpised no one else in the cummunity replied to this request appart from you. In my understanding the % Running Total calculation should work regardless of the sorted column. Am I missing somehting or no one really can help us in this?

Regards,

Reza

[MFelix]

Hi @Reza,

Sorry for the late response are you making this on PBI or on Power Pivot, what is the formula you are using for the cummulative?

Just to know how I can make the changes in your running total.

Regards,

MFelix

[Reza]

Hi @MFelix,

Thanks for the solution. This works as you have suggested, the only problem I have is, if I sort the table/chart based on the 'Section Count' (descending) and not 'Section' the "% Running Total" does not work correctly. Is there anyway to achieve this?

Sorted on "Section Count" Descending

Regards,

Reza

[Reza]

Hi @MFelix,

I am afraid but the same result with ALL

Regards,

Reza