Display table in matrix

[Tevon713]

Hi all..

I'm struggling to display a simple in matrix output as follow. Can't seem to wrap my head around it. How would I achieve this?

Data:

Month	Year	BU	Ops Day	FTE Count	FTE Cost
Jan YTD	2022	ABC	209	16	1000000
Jan YTD	2023	ABC	209	20	1500000

Desire output:

[Arul · Accepted solution]

@Tevon713 ,

try the below,

1. Create a static Table like the below by using attached M code,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgtxVXDOL80rUdJRMlSK1YGJFJcoqACFjJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Metrics = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Metrics", type text}, {"Index", Int64.Type}})
in
    #"Changed Type"

2. Then load the data to Power BI desktop and then sort the metric column based on index column.
3. Then, Write the below 4 measures by changing the actual table of your dataset and use it in matrix visual.

Previous Year = 
VAR _previousYear = MAX('Test Matrix'[Year])-1
VAR _resultFTECostPreviousYear = CALCULATE(
    SUM('Test Matrix'[FTE Cost]),
    'Test Matrix'[Year] = _previousYear)
VAR _resultFTECountPreviousYear = CALCULATE(
    SUM('Test Matrix'[FTE Count]),
    'Test Matrix'[Year] = _previousYear)
VAR _result = 
            SWITCH(SELECTEDVALUE('Static'[Metrics]),
            "FTE Count",_resultFTECountPreviousYear,
            "FTE Cost $",_resultFTECostPreviousYear)
RETURN _result

Current Year = 
VAR _currentYear = MAX('Test Matrix'[Year])
VAR _resultFTECostCurrentYear = CALCULATE(
    SUM('Test Matrix'[FTE Cost]),
    'Test Matrix'[Year] = _currentYear)
VAR _resultFTECountCurrentYear = CALCULATE(
    SUM('Test Matrix'[FTE Count]),
    'Test Matrix'[Year] = _currentYear)
VAR _result = 
            SWITCH(SELECTEDVALUE('Static'[Metrics]),
            "FTE Count",_resultFTECountCurrentYear,
            "FTE Cost $",_resultFTECostCurrentYear)
RETURN _result

% = 
VAR _previousYear = MAX('Test Matrix'[Year])-1
VAR _resultFTECostPreviousYear = CALCULATE(
    SUM('Test Matrix'[FTE Cost]),
    'Test Matrix'[Year] = _previousYear)
VAR _resultFTECountPreviousYear = CALCULATE(
    SUM('Test Matrix'[FTE Count]),
    'Test Matrix'[Year] = _previousYear)
VAR _currentYear = MAX('Test Matrix'[Year])
VAR _resultFTECostCurrentYear = CALCULATE(
    SUM('Test Matrix'[FTE Cost]),
    'Test Matrix'[Year] = _currentYear)
VAR _resultFTECountCurrentYear = CALCULATE(
    SUM('Test Matrix'[FTE Count]),
    'Test Matrix'[Year] = _currentYear)
VAR _differenceFTECount = _resultFTECountCurrentYear-_resultFTECountPreviousYear
VAR _differenceFTECost = _resultFTECostCurrentYear-_resultFTECostPreviousYear
VAR _result = 
            SWITCH(SELECTEDVALUE('Static'[Metrics]),
            "FTE Count",DIVIDE(_differenceFTECount,_resultFTECountPreviousYear),
            "FTE Cost $",DIVIDE(_differenceFTECost,_resultFTECostPreviousYear))
RETURN _result

Difference = 
VAR _previousYear = MAX('Test Matrix'[Year])-1
VAR _resultFTECostPreviousYear = CALCULATE(
    SUM('Test Matrix'[FTE Cost]),
    'Test Matrix'[Year] = _previousYear)
VAR _resultFTECountPreviousYear = CALCULATE(
    SUM('Test Matrix'[FTE Count]),
    'Test Matrix'[Year] = _previousYear)
VAR _currentYear = MAX('Test Matrix'[Year])
VAR _resultFTECostCurrentYear = CALCULATE(
    SUM('Test Matrix'[FTE Cost]),
    'Test Matrix'[Year] = _currentYear)
VAR _resultFTECountCurrentYear = CALCULATE(
    SUM('Test Matrix'[FTE Count]),
    'Test Matrix'[Year] = _currentYear)
VAR _result = 
            SWITCH(SELECTEDVALUE('Static'[Metrics]),
            "FTE Count",_resultFTECountCurrentYear-_resultFTECountPreviousYear,
            "FTE Cost $",_resultFTECostCurrentYear-_resultFTECostPreviousYear)
RETURN _result

Result:

Thanks,

Arul

[Tevon713]

Thank you @Arul. If I have more metrics I will need to create more VAR for each in those more 4 measures to display properly. 

[Arul]

@Tevon713 ,

What are you mentioning as a metrics here exactly?

[Tevon713]

Those are sample for FTE Count and FTE Cost, expecting least 20 other metrics of comparison. Right now I have 2 separate table for current and previous year due to the diff BU. 

[Arul]

@Tevon713 ,

You have to manually add all the other 20 metrics in static table and do a comparison in all the 4 measures.

Thanks,

Arul

[Tevon713]

Thanks @Arul. Make sense. One last question, how would I add a header per each section in the matrix? ie FTE, $, KPI headers. Would I add this in the static table?

[Arul]

@Tevon713 ,

No. You have do it differently but i am not sure how we can exactly do.

Thanks,

Arul