topic Re: Matrix table with sum in DAX Commands and Tips

Matrix table with sum

Carlo1975 — Wed, 16 Dec 2020 11:55:39 GMT

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

Re: Matrix table with sum

CNENFRNL — Wed, 16 Dec 2020 14:43:29 GMT

Hi, Carlo1975 , do you expect this?

You might want to add another measure,

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

Re: Matrix table with sum

Carlo1975 — Wed, 16 Dec 2020 15:27:24 GMT

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

Re: Matrix table with sum

CNENFRNL — Wed, 16 Dec 2020 17:09:11 GMT

Carlo1975 , you might want to try

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

Re: Matrix table with sum

Carlo1975 — Wed, 16 Dec 2020 17:26:26 GMT

I'm sorry, what is Cluster2?