Forum Discussion

wwolak's avatar
wwolak
Frequent Visitor
5 months ago
Solved

Matrix with dynamic values

Hi, I want to create a matrix as follows: I'd like the values for Current Stock, Safety Stock and Available Stock to change dynamically, based on unit selected in a slicer (two options pcs an...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    1) Create a disconnected table for the matrix columns

    Matrix Columns =
VAR FixedCols =
    DATATABLE (
        "ColType", STRING,
        "Label", STRING,
        "MonthStart", DATE,
        {
            { "CURRENT", "Current Stock", BLANK() },
            { "SAFETY",  "Safety Stock",  BLANK() }
        }
    )
VAR MonthCols =
    SELECTCOLUMNS (
        VALUES ( 'Date'[MonthStart] ),          -- use your month start/date key
        "ColType", "MONTH",
        "Label", FORMAT ( 'Date'[MonthStart], "yyyy-MM" ),
        "MonthStart", 'Date'[MonthStart]
    )
RETURN
    UNION ( FixedCols, MonthCols )

    Then sort Matrix Columns[Label] by Matrix Columns[MonthStart] (and keep fixed ones first with an additional sort column if needed).

     

    2) Use THIS in the Matrix

     

    • Rows: Region, Country, Material (your dims)

    • Columns: Matrix Columns[Label]

    • Values: a single measure like below

     

    3) One measure that switches logic per column

    Matrix Stock Value =
VAR _type = SELECTEDVALUE ( 'Matrix Columns'[ColType] )
VAR _month = SELECTEDVALUE ( 'Matrix Columns'[MonthStart] )
RETURN
SWITCH (
    _type,
    "CURRENT",
        CALCULATE ( [Current Stock], REMOVEFILTERS ( 'Date' ) ),
    "SAFETY",
        CALCULATE ( [Safety Stock], REMOVEFILTERS ( 'Date' ) ),
    "MONTH",
        CALCULATE (
            [Available Stock],
            TREATAS ( { _month }, 'Date'[MonthStart] )
        )
)

     

MFelix's avatar
MFelix
Super User
5 months ago

Hi wwolak ,

 

Have you tried using Calculation groups? This will allow to have a calculation based on the selection you have in this case no need to do the switch statment.

 

You can create 3 generic measures like:

Safety Stock = 1
Current Stock = 1
Available Stock = 1

Be aware that you can also use the correct calculations has you need then because when you apply the Calculation group they will overwrite the result of the measure in the matrix.

 

Now create the calculation groups with the following syntax:

Pcs = 
Var _SafetyStock = SUM(fact_Inventory[SafetyStock])
Var _CurrentStock = SUM(fact_Inventory[CurrentStock])
Var _AvailableStock= SUM(fact_Inventory[AvailableStock])
RETURN
      SWITCH( TRUE(),
ISSELECTEDMEASURE([Safety Stock]), _SafetyStock,
ISSELECTEDMEASURE([Current Stock]), _CurrentStock,
ISSELECTEDMEASURE([Available Stock]), _AvailableStock)

 

Be aware that you need to change the calculations of each value for the correct one then add another code with the kgs, now you can use it on the matrix and the measure will calculate properly.

 

https://learn.microsoft.com/en-us/power-bi/transform-model/calculation-groups