Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to Solution.
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
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
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.
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.
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?
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |