Forum Discussion

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

Square Matrix

Hi everyone! 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 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi pg1980 
    Please refer to attached sample file with the proposed solution

    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
    )
RETURN
    QtyNew - QtyOut

    This is based on the assumption that year-month is date format (start of month). Also the expected results of July do not seem to be reasonable as the sample data data says 1 in in October and 1 out in October. However I have adjusted the sample data so that 1 in in July and 1 out in October.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

pg1980 

Yes I could see that in your expected result but how did you cone up with that reault? I could not see the logic behind it?

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

Yes, you are right. I didn´t put a value for 2023-07. I have just corrected the table.

 

And if i have to do like 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  15
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

 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    pg1980 
    See attached updated sample file

    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 )
    • pg1980's avatar
      pg1980
      Icon for Helper II rankHelper II
      2 years ago

       And if i have to use also the "total" row with the same logic?

       

         0    1    2    3    4    5    6    7    8    9
      2023-05  792  775  766  764  742556  238  13  13  13
      2023-06  204  204  69  53  9  6  5  5  5  5

       

      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)