Percentage of Running Total for a Matrix

[goncalogeraldes]

Hello there! I am trying to obtain the running total for the percentagem of the total value. I have tried many solutions that I found here in the community but they all turn out like in the image. That is, it is calculating "correctly" but not in the correct order. What I want in the first row is 21,89%, second row 42,69, third row 52,84 and so on... Currently it is calculating the percentages in alphabetical order but I need it by the total sum (please note that the "€" is sorted)

Thanks in advance!! 

Gonçalo Costa

[v-stephen-msft]

Hi @goncalogeraldes ,

Could you tell me if your problem has been solved?
If it is, kindly Accept the useful reply as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[goncalogeraldes]

Unfortunately I still do not have a solution to this... I have tried many methods but none seems to work... Thanks for your suggestions though!

[PaulDBrown]

Create a measure for the total sales, using something along the lines of:

Total sales by customer= CALCULATE ([Sum sales], ALL(table[Customer name))

and finally use your current running total in 

% running total = DIVIDE( [your running total measure], [Total sales by customer])

[goncalogeraldes]

@PaulDBrown Thanks for your reply for unfortunately this wont work. The problem is the same as in the print I sent the response to nvprasad reply

[PaulDBrown]

There must be something going on with the filter context. Here is a quick example:

Cumul. sales by product ref =
VAR Rnk = [RANK Sales by Product Ref]
RETURN
    IF (
        ISINSCOPE ( 'DIM Product Ref'[Product REF] ),
        CALCULATE (
            [Sum of Sales],
            FILTER (
                ALL ( 'DIM Product Ref'[Product REF] ),
                [RANK Sales by Product Ref] <= Rnk
            )
        )
    )

Sales all prod ref = CALCULATE([Sum of Sales], ALL('DIM Product Ref'[Product REF]))

% cumulative = DIVIDE([Cumul. sales by product ref], [Sales all prod ref])

[nvprasad]

Hi goncalogeraldes,

I am hoping you are trying to calculate running % based on date.

Running%Total =

Var RunningTotal = CALCULATE([Sales Amount],FILTER(ALL('Date'),'Date'[Date]<=MIN('Date'[Date])))

Var TotalSales = CALCULATE([Sales Amount],ALL('Date'))

Var result = DIVIDE(RunningTotal,TotalSales,0)

Return result

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[goncalogeraldes]

@nvprasad Thanks for the fast response! The problem here is that this based on the Customer Name and not the Date column so unfortunately this wont work... This one is actually quite similar to the one I have now!

[nvprasad]

Hi,

Could you please check with the below logic.

RunningTotal%Check =

Var rankcheck = RANKX ( ALL ( 'Product'[Product Name] ), [Sales Amount],, DESC )

Var runningTotal = CALCULATE([Sales Amount], FILTER(ALL('Product'[Product Name]),RANKX ( ALL ( 'Product'[Product Name] ), [Sales Amount],, DESC )<=rankcheck))

Var totalSales = CALCULATE([Sales Amount],ALL('Product'[Product Name]))

Return DIVIDE(runningTotal,totalSales,0)

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[goncalogeraldes]

Hi there @nvprasad ! Thanks for the help! Unfortunately this is the result with your formula...

Any ideas on what is hapenning? 

[goncalogeraldes]

@nvprasad I swithched the order of the "rankcheck" and the "runningtotal" and it makes a little bit more sense but it gives the opposite result of what i need. I think its almost there! Here is the result:

I need it reversed!