Forum Discussion

pg1980's avatar
pg1980
Icon for Helper II rankHelper II
3 years ago
Solved

Percentage Square Matrix (with Total row)

Hi !

I have to be able to make a analysis in a matrix visual. In this analysis I have to sum up the quantity values by “year-month New” and go substracting this number to the total quantity of each month “year-month Out”.

 

Table:

 

Product  Quantity  year-month New  year-month Out
AA  52023-06 
AA  132023-05 
AA  12023-07 
AA  12023-062023-11
AA  12023-062023-10
AA  22023-062023-10
AA  22023-062023-09
AA  422023-062023-09
AA  62023-062023-08
AA  102023-062023-08
AA  532023-062023-07
AA  822023-062023-07
AA  22023-052023-07
AA  12023-052023-06
AA  12023-052023-06
AA  32023-052023-06
AA  42023-052023-06
AA  132023-052023-05
AA  42023-052023-05
AA  42023-052023-10
AA  1412023-052023-09
AA  452023-052023-09
AA  142023-052023-08
AA  82023-052023-08
AA  3142023-052023-10
AA  2252023-052023-11

 

For example, for the row 2023-06 (from “year-month New”)  I have a total sum of 204 and this is being substracted per month for the following month . This is the total result minus the quantity of 2023-07 (from “year-month Out”: 82+53) ==> 204 – 135 = 69.
The last value ("5") has to repeat until de last month (“year-month Out")

 

Total quantity (using year-month New):

 

 TOTAL
2023-05  792
2023-06  204
2023-07  1

 

Result:

 

   1  2  3  4  5  6  7  8  9  10  11  12  13  14  15
2023-05  775  766  764  742  556  238  13  13  13  13  13  13  13  13  13
2023-06  204  69  53  9  6  5  5  5  5  5  5  5  5  5  5
2023-07  1  1  1  1  1  1 1 1 1 1 1 1 1 1   1

 

 

When i need the % of the square Matrix, i need this:

 

     1    2    3    4    5    6    7    8    9    10    11    12    13    14    15
2023-05:    (775/792)    (766/775)    (764/766)   (742/764)   (556/742)   (238/556)     (13/238)  (13/13)  (13/13)  (13/13)  (13/13)  (13/13)  (13/13)  (13/13)  (13/13)
2023-06:    (204/204)  (69/204)  (53/69)  (9/53)  (6/9)  (5/6)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)

 

In column n° 1: you have to divide the first value from the total --> 775 / 792= 0,97
In column n° 2: you have to divide the second value with the first value ->  766/775= 0.98
in column n°3 : you have to divide the third value with the second value ->   764/766
in column n°4 : you have to divide the forth value with the thrd value ->   742/764

 

    1 
  2  3  4 5  6 7  8  9  10  11  12  13  14
2023-05:  0,98  0,99  1  0,97  0,75  0,43  0,05  1  1  1  1  1  1  1  1
2023-06:  1  0,34  0,77  0,17  0,67  0,83  1  1  1  1  1  1  1  1  1

 

And the last thing i need in this measure is to sumarize  per column and divide by the previous one:

 

 

0

1

2

3

4

5

6

7

8

9

10

Total 

(792

+

204)

(775

+

204)

/

(792

+

204)

(766

+

69)

/

(774

+

204)

(764

+

53)

/

(766

+

69)

(742 +

9)

/

(764

+

53)

(556 +

6)

/

(742

+

9)

(238 +

5)

/

(556

+

6)

(13

+

5)

/

(238

+

5)

(13

+

5)

/

(13

+

5)

(13

+

5)

/

(13

+

5)

(13

+

5)

/

(13

+

5)

 

And i need this result:

 

      1     2    3    4   5    6   7    8    9    10
2023-05:    0,98    0,99    1    0,97    0,75    0,43  0,05    1    1    1
2023-06:    1    0,34    0,77    0,17    0,67    0,83  1    1    1    1
Total    0,98    0,85    0,98    0,92    0,75    0,43  0,07    1    1    1

 

 

 

 

  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi pg1980 

    See attached PBIX.

    I haven't looked closely at other responses so may be repeating things 🙂

     

    Model:

    Month Index is the number of months between New & Out plus 1.

    Key measures:

    Total Quantity = 
SUM ( 'YourTable'[Quantity] )

Quantity Out Cumulative by Index = 
VAR MaxIndex =
    MAX ( 'Month Index'[Month Index] )
RETURN
    CALCULATE (
        [Total Quantity],
        'Month Index'[Month Index] <= MaxIndex,
        NOT ISBLANK ( 'Month Index'[Month Index] ),
        REMOVEFILTERS ( 'Month Index' )
    )

Total Quantity All Index = 
CALCULATE (
    [Total Quantity],
    REMOVEFILTERS ( 'Month Index' )
)

Net Quantity Cumulative by Index = 
[Total Quantity All Index] - [Quantity Out Cumulative by Index]

Net Quantity Cumulative by Index Previous = 
VAR MaxIndex =
    MAX ( 'Month Index'[Month Index] )
RETURN
    CALCULATE (
        [Net Quantity Cumulative by Index],
        'Month Index'[Month Index] = MaxIndex - 1,
        REMOVEFILTERS ( 'Month Index'[Month Index])
    )

Percentage = 
DIVIDE (
    [Net Quantity Cumulative by Index],
    [Net Quantity Cumulative by Index Previous]
)

     

18 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    3 years ago

    Hello pg1980,

     

    Can you please try:

     

    1. Create a Date Table: If you don't already have a date table in your Power BI model, it's essential to create one. 

    2. Create a Matrix Table with DAX

    MatrixTable =
VAR TotalTable =
    SUMMARIZE('YourTable', 'YourTable'[year-month New], "TotalQuantity", SUM('YourTable'[Quantity]))

RETURN
SUMMARIZE(
    ADDCOLUMNS(
        FILTER(TotalTable, 'YourTable'[year-month New] = MAX('DateTable'[Date])),
        "MonthOffset",
        DATEDIFF(MIN('YourTable'[year-month Out]), 'YourTable'[year-month New], MONTH)
    ),
    'YourTable'[year-month New],
    "MatrixValue",
    IF(ISBLANK([MonthOffset]), [TotalQuantity], [TotalQuantity] - SUMX(FILTER(TotalTable, [MonthOffset] = -1), [TotalQuantity]))
)

    3. Create Percentage Calculations

    PercentageMeasure =
VAR CurrentRowMonth = SELECTEDVALUE('MatrixTable'[year-month New])
VAR PreviousRowMonth = CALCULATE(MAX('MatrixTable'[year-month New]), 'MatrixTable'[year-month New] < CurrentRowMonth)
VAR CurrentRowTotal = CALCULATE(SUM('MatrixTable'[MatrixValue]), 'MatrixTable'[year-month New] = CurrentRowMonth)
VAR PreviousRowTotal = CALCULATE(SUM('MatrixTable'[MatrixValue]), 'MatrixTable'[year-month New] = PreviousRowMonth)
RETURN
IF(ISBLANK(PreviousRowMonth), 1, CurrentRowTotal / PreviousRowTotal)

    4. Now, you can build a matrix visual using the "MatrixTable" table as your source

     

    Should you require further details or information, please do not hesitate to reach out to me.

    • pg1980's avatar
      pg1980
      Icon for Helper II rankHelper II
      3 years ago

      Hello Sahir_Maharaj ,

       

      Thanks four your answer but i have a problem when i create the table:

       

       

      TotalQuantity cannot be found and neither MonthOffset. Can you help me with that? So i can try the PercentageMeasure 

       

      thanks!

    • pg1980's avatar
      pg1980
      Icon for Helper II rankHelper II
      3 years ago

      I have this measure but it doesn't do correctly the total row (to sumarize  per column and divide by the previous one)

       

      Current Quantity = 
VAR MonthNew = SELECTEDVALUE ( 'Table'[year-month New] )
VAR MonthsShift = SELECTEDVALUE ( Months[Value] )
VAR MonthOut = EOMONTH ( MonthNew, MonthsShift - 2 ) + 1
VAR QtyNew = SUM ( 'Table'[Quantity] )
VAR QtyOut = 
    CALCULATE ( 
        SUM ( 'Table'[Quantity] ), 
        COALESCE ( 'Table'[year-month Out], DATE ( 2050, 1, 1 ) ) <= MonthOut
    )
VAR QtyOutAfter = 
    CALCULATE ( 
        SUM ( 'Table'[Quantity] ), 
        COALESCE ( 'Table'[year-month Out], DATE ( 2050, 1, 1 ) ) < MonthOut 
    )
RETURN 
    DIVIDE ( QtyNew - QtyOut, QtyNew - QtyOutAfter )

       

      And i need this result:

       

       

            1     2    3    4   5    6   7    8    9    10
      2023-05:    0,98    0,99    1    0,97    0,75    0,43  0,05    1    1    1
      2023-06:    1    0,34    0,77    0,17    0,67    0,83  1    1    1    1
      Total    0,98    0,85    0,98    0,92    0,75    0,43  0,07    1    1    1
  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    3 years ago

    Please try to create a new measure for the Total Quantity:
    Total Quantity =
    SUM('YourTable'[Quantity])
    Create a new measure for the Matrix Table:
    Matrix Table =
    VAR MaxDate = MAX('YourTable'[year-month New])
    RETURN
    SUMMARIZE(
    ADDCOLUMNS(
    GENERATE(
    VALUES('YourTable'[year-month New]),
    FILTER('YourTable', 'YourTable'[year-month New] = EARLIER('YourTable'[year-month New]))
    ),
    "MonthOffset", DATEDIFF(MIN('YourTable'[year-month Out]), 'YourTable'[year-month New], MONTH)
    ),
    'YourTable'[year-month New],
    "MatrixValue",
    IF(ISBLANK([MonthOffset]), [Total Quantity], [Total Quantity] - SUMX(FILTER(ALL('YourTable'), 'YourTable'[year-month Out] <= EARLIER('YourTable'[year-month New])), [Total Quantity]))
    )
    Create a new measure for the Percentage Calculation:
    Percentage Measure =
    VAR CurrentRowMonth = SELECTEDVALUE('Matrix Table'[year-month New])
    VAR PreviousRowMonth = CALCULATE(MAX('Matrix Table'[year-month New]), 'Matrix Table'[year-month New] < CurrentRowMonth)
    VAR CurrentRowTotal = CALCULATE(SUM('Matrix Table'[MatrixValue]), 'Matrix Table'[year-month New] = CurrentRowMonth)
    VAR PreviousRowTotal = CALCULATE(SUM('Matrix Table'[MatrixValue]), 'Matrix Table'[year-month New] = PreviousRowMonth)
    RETURN
    IF(ISBLANK(PreviousRowMonth), 1, CurrentRowTotal / PreviousRowTotal)
    Now, you can build a matrix visual using the "Matrix Table" table as your source, and the "Percentage Measure" measure for the values.

    • pg1980's avatar
      pg1980
      Icon for Helper II rankHelper II
      3 years ago

      thanks technolog for your answer but when i create this measure, i have this error:

       

       

      • technolog's avatar
        technolog
        Icon for Super User rankSuper User
        3 years ago

        Matrix Table =
        VAR MaxDate = MAX('YourTable'[year-month New])
        RETURN
        SUMMARIZE(
        ADDCOLUMNS(
        'YourTable',
        "MonthOffset", DATEDIFF(MIN('YourTable'[year-month Out]), 'YourTable'[year-month New], MONTH)
        ),
        'YourTable'[year-month New],
        "MatrixValue",
        IF(ISBLANK([MonthOffset]), [Total Quantity], [Total Quantity] - SUMX(FILTER(ALL('YourTable'), 'YourTable'[year-month Out] <= EARLIER('YourTable'[year-month New])), [Total Quantity]))
        )

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    Hi pg1980 

    See attached PBIX.

    I haven't looked closely at other responses so may be repeating things 🙂

     

    Model:

    Month Index is the number of months between New & Out plus 1.

    Key measures:

    Total Quantity = 
SUM ( 'YourTable'[Quantity] )

Quantity Out Cumulative by Index = 
VAR MaxIndex =
    MAX ( 'Month Index'[Month Index] )
RETURN
    CALCULATE (
        [Total Quantity],
        'Month Index'[Month Index] <= MaxIndex,
        NOT ISBLANK ( 'Month Index'[Month Index] ),
        REMOVEFILTERS ( 'Month Index' )
    )

Total Quantity All Index = 
CALCULATE (
    [Total Quantity],
    REMOVEFILTERS ( 'Month Index' )
)

Net Quantity Cumulative by Index = 
[Total Quantity All Index] - [Quantity Out Cumulative by Index]

Net Quantity Cumulative by Index Previous = 
VAR MaxIndex =
    MAX ( 'Month Index'[Month Index] )
RETURN
    CALCULATE (
        [Net Quantity Cumulative by Index],
        'Month Index'[Month Index] = MaxIndex - 1,
        REMOVEFILTERS ( 'Month Index'[Month Index])
    )

Percentage = 
DIVIDE (
    [Net Quantity Cumulative by Index],
    [Net Quantity Cumulative by Index Previous]
)