Matrix with dynamic values

[wwolak]

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 and kgs). Additionally I want values for Available Stock to be filtered by Year-Month placed in columns of the matrix, while Current Stock and Safety Stock remain unfiltered.
I already have datatable to be used in a slicer and dynamic measures for all three "Stock options", as follows:

Safety Stock = 
VAR _pcs = SUM(fact_Inventory[SafetyStock])
VAR _kgs = SUMX(fact_Inventory, fact_Inventory[SafetyStock] * fact_Inventory[Multiplication])
RETURN
      SWITCH( SELECTEDVALUE ('Unit Selector'[Masure]),
                     "pcs", _pcs,
                     "kgs", _kgs,
                      _pcs)

As for now I can place all the measures in the values of the matrix but they appear for every Year_Month column, but that's not what I want.
Any ideas how to achieve the desired result? I'd really appreciate your support on that.

Thanks in advance for help!

[v-nmadadi-msft]

Hi @wwolak 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @wwolak 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[cengizhanarslan · Accepted solution]

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]

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