Matrix table with sum

[Carlo1975]

Hi, I have a problem and I don't know how to overcome it. I don't know if the solution is Dax or power query. I'm sorry.

I try to explain.

I have a table like this. With 2 clusters columns, one column with the quarters, and one with an amount:

If I try to do a matrix table I don't have any problem

up to here everything is quite simple.

The problem is that, for each row, I have to sum the previous value with the following. And so, if you look at first row, you have for the first quarter 150, the second 150 and on the last one 700:

In my new Matrix Table I have to do this: On the first quarter I will have 150, on the second quarter 300 (because I have to sum the 150 on the first quarter, to the second). In the third quarter, I will have 1000 because I will sum 150 (first quarter), 150 (second quarter) and 700 (last one). The new first row will be:

This one, for all the row and all the quarters.

Ok....for this problem CNENFRNL said me the best solution and I used it:

Accum = 
VAR __qtr = MAX ( Table1[Quarter] )
RETURN
    CALCULATE ( SUM ( Table1[Amount] ), Table1[Quarter] <= __qtr )

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Matrix-table-with-sum/m-p/1521406#M29672

But now I have another problem....I don't have to use absolute numbers but percentages and I cannot use the power BI options "show value as a percentage of grand total".

Is it possible using the DAX code above and my example to have a percentage cumulative Value?

thank you

Carlo

[CNENFRNL · Accepted solution]

@Carlo1975 , you might want to try

% Accum =
VAR __gt = SUMX ( ALL ( Table1[Cluster2], Table1[Quarter] ), [Accum] )
RETURN
    DIVIDE ( [Accum], __gt )

[Carlo1975]

I'm sorry, what is Cluster2?

[CNENFRNL]

Hi, @Carlo1975 , do you expect this?

You might want to add another measure,

% Accum = 
DIVIDE( [Accum], CALCULATE( SUM( Table1[Amount] ) , ALL( Table1[Quarter] ) ) )

[Carlo1975]

HI CNENFRNL and Thank you....with this I obtain always 100% on my chart, instead I have to have this:

And so each percentage, is a percentage on the grand total (4.190)...the sum of all the percentage will be 100%

150 is the 4% of grand total 4.190

300 is the 7% of grand total 4.190

I hope to explain well my problem.

thank you

Carlo