Percentage Square Matrix (with Total row)

[pg1980]

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	5	2023-06
AA	13	2023-05
AA	1	2023-07
AA	1	2023-06	2023-11
AA	1	2023-06	2023-10
AA	2	2023-06	2023-10
AA	2	2023-06	2023-09
AA	42	2023-06	2023-09
AA	6	2023-06	2023-08
AA	10	2023-06	2023-08
AA	53	2023-06	2023-07
AA	82	2023-06	2023-07
AA	2	2023-05	2023-07
AA	1	2023-05	2023-06
AA	1	2023-05	2023-06
AA	3	2023-05	2023-06
AA	4	2023-05	2023-06
AA	13	2023-05	2023-05
AA	4	2023-05	2023-05
AA	4	2023-05	2023-10
AA	141	2023-05	2023-09
AA	45	2023-05	2023-09
AA	14	2023-05	2023-08
AA	8	2023-05	2023-08
AA	314	2023-05	2023-10
AA	225	2023-05	2023-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 · Accepted solution]

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]
)

[technolog]

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]

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

[technolog]

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]))
)

[pg1980]

Sorry but it is the same. it doesn't work "MonthOffset":

I created as a measure, not as a new table. It´s ok?

[technolog]

In this case you can try add this column in Power Query Editor.

1.Click on the "Add Column" tab and then select "Custom Column."

In the "Custom Column" dialog, enter a name for your new column (e.g., "MonthOffset").

2. In the formula field, enter the following formula:

Date.Month([year-month New]) - Date.Month([year-month Out])

update DAX:
Matrix Table =
VAR MaxDate = MAX('YourTable'[year-month New])
RETURN SUMMARIZE( 'YourTable', '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])) )

[pg1980]

I added this column by PQ and y update the measure but i have a similar error:

and if i replace the

"IF(ISBLANK([MonthOffset])" by  "IF('YourTable'[year-month New]=BLANK()", i have this error:

Matrix Table =

VAR MaxDate = MAX('YourTable'[year-month New])

RETURN SUMMARIZE( 'YourTable', 'YourTable'[year-month New], "MatrixValue", IF('YourTable'[year-month New]=BLANK(), [Total Quantity], [Total Quantity] - SUMX(FILTER(ALL('YourTable'), 'YourTable'[year-month Out] <= EARLIER('YourTable'[year-month New])), [Total Quantity])) )

[technolog]

@pg1980 are you creating calculated table?

[pg1980]

I tried to create as a Measure and as a Calculated table, but it is not working.

As a Calculated table:

[technolog]

please try add table name

[pg1980]

it is also not working:

option 1: IF('YourTable'[MonthOffset]=BLANK()

option 2: IF(ISBLANK('YourTable'[MonthOffset])

[lbendlin]

might as well throw my version into the ring.

[pg1980]

Hello @lbendlin,  thanks for your answer but I find a diferent result in the % total row:

In column 2 and 3, the results are diferent:

For column n° 2: 836/980=0,85

For  column n° 3: 818/836=0,97

[lbendlin]

You can also see that my totals are off by 1.  Can we eliminate the July sample data for now?

[pg1980]

It's almost there, but the matrix has to repeat the value that doen't have "year-month out"

For example in May ("Year month new") the value 13 has to repeat util the last month.

like this:

[Sahir_Maharaj]

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]

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

[pg1980]

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!