topic Re: Percentage Square Matrix (with Total row) in DAX Commands and Tips

Percentage Square Matrix (with Total row)

pg1980 — Tue, 19 Sep 2023 13:13:46 GMT

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:

2023-05:

(775/792)

(766/775)

(764/766)

(742/764)

(556/742)

(238/556)

(13/238)

(13/13)

2023-06:

(204/204)

(69/204)

(53/69)

(9/53)

(6/9)

(5/6)

(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:

Total

(792

204)

(775

204)

(792

204)

(766

69)

(774

204)

(764

53)

(766

69)

(742 +

(764

53)

(556 +

(742

(238 +

(556

(13

(238

(13

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

Re: Percentage Square Matrix (with Total row)

Sahir_Maharaj — Sun, 17 Sep 2023 17:31:51 GMT

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.

Re: Percentage Square Matrix (with Total row)

pg1980 — Mon, 18 Sep 2023 12:38:47 GMT

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!

Re: Percentage Square Matrix (with Total row)

pg1980 — Tue, 19 Sep 2023 13:14:37 GMT

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

Re: Percentage Square Matrix (with Total row)

technolog — Wed, 27 Sep 2023 14:37:07 GMT

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.

Re: Percentage Square Matrix (with Total row)

pg1980 — Wed, 27 Sep 2023 15:05:04 GMT

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

Re: Percentage Square Matrix (with Total row)

technolog — Wed, 27 Sep 2023 15:10:06 GMT

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

Re: Percentage Square Matrix (with Total row)

pg1980 — Wed, 27 Sep 2023 15:17:33 GMT

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

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

Re: Percentage Square Matrix (with Total row)

technolog — Wed, 27 Sep 2023 15:37:53 GMT

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

Re: Percentage Square Matrix (with Total row)

pg1980 — Wed, 27 Sep 2023 17:04:24 GMT

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

Re: Percentage Square Matrix (with Total row)

technolog — Wed, 27 Sep 2023 17:21:04 GMT

pg1980 are you creating calculated table?

Re: Percentage Square Matrix (with Total row)

pg1980 — Wed, 27 Sep 2023 17:29:16 GMT

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

As a Calculated table:

Re: Percentage Square Matrix (with Total row)

technolog — Wed, 27 Sep 2023 17:33:21 GMT

please try add table name

Re: Percentage Square Matrix (with Total row)

pg1980 — Wed, 27 Sep 2023 17:55:54 GMT

it is also not working:

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

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

Re: Percentage Square Matrix (with Total row)

lbendlin — Fri, 29 Sep 2023 01:15:41 GMT

might as well throw my version into the ring.

Re: Percentage Square Matrix (with Total row)

pg1980 — Fri, 29 Sep 2023 13:04:23 GMT

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

Re: Percentage Square Matrix (with Total row)

lbendlin — Fri, 29 Sep 2023 16:24:38 GMT

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

Re: Percentage Square Matrix (with Total row)

pg1980 — Fri, 29 Sep 2023 17:31:34 GMT

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:

Re: Percentage Square Matrix (with Total row)

OwenAuger — Thu, 12 Oct 2023 06:17:19 GMT

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