How to create accumulate % for 8020 Table ?

[Paulyeo11]

Hi All

Below my raw data :-

https://www.dropbox.com/s/v2eypdrj51k4yvd/8020%20V001.pbix?dl=0

Expected result in yellow :-

[Icey · Accepted solution]

Hi @Paulyeo11 ,

Try this:

1. Add an index column in Power Query Editor.

2. Create measures or columns.

• Measures:

ACC TOTAL Measure = 
CALCULATE (
    SUM ( 'Table (2)'[SALES] ),
    FILTER (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Index] <= MAX ( 'Table (2)'[Index] )
    )
)

ACC PER CENT Measure = 
VAR Sum_ =
    CALCULATE ( SUM ( 'Table (2)'[SALES] ), ALLSELECTED ( 'Table (2)' ) )
RETURN
    DIVIDE ( [ACC TOTAL Measure], Sum_, 0 )

• Columns:

ACC TOTAL Column = 
CALCULATE (
    SUM ( 'Table (2)'[SALES] ),
    FILTER (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Index] <= EARLIER( 'Table (2)'[Index] )
    )
)

ACC PER CENT Column = 
VAR Sum_ =
    SUM ( 'Table (2)'[SALES] )
RETURN
    DIVIDE ( [ACC TOTAL Column], Sum_, 0 )

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Paulyeo11 , Try like

New Measure , assume [Acc Total] is a measure

divide([Acc Total], calculate(sum(Table[Sales]), all(Table)))

or

divide([Acc Total], calculate(sum(Table[Sales]), allselected(Table)))

[Paulyeo11]

Hi Amit

For my actual raw data , only have field name = SALES , still dont have ACC TOTAL.

https://www.dropbox.com/s/v2eypdrj51k4yvd/8020%20V001.pbix?dl=0

[amitchandak]

@Paulyeo11 , Try like

divide(Sum(Table[Acc Total]), calculate(sum(Table[Sales]), all(Table)))

[Paulyeo11]

Hi Amit

In my aactual data , i still dont have Acc Total field :-

divide(Sum(Table[Acc Total]), calculate(sum(Table[Sales]), all(Table)))

Paul

[amitchandak]

Please find the file.

[Paulyeo11]

Hi Amit

Very sorry for send my sample file with extra field , below is only 2 field in my PBI file :-

kindly open my below actual raw data file :-

https://www.dropbox.com/s/48v9ajfgormrk6c/8020%20V001a.pbix?dl=0

and hope that you can help me get the accumulated % 

meaning you need 2 expression , first to get the ACC Total field , then get the % field.

Paul