Forum Discussion

Carlo1975's avatar
Carlo1975
Helper I
5 years ago
Solved

Matrix table with sum

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's avatar
    CNENFRNL
    5 years ago

    Carlo1975 , you might want to try

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

     

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, Carlo1975 , do you expect this?

    You might want to add another measure,

    % Accum = 
DIVIDE( [Accum], CALCULATE( SUM( Table1[Amount] ) , ALL( Table1[Quarter] ) ) )
    • Carlo1975's avatar
      Carlo1975
      Helper I
      5 years ago

      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

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Carlo1975 , you might want to try

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