Forum Discussion

sas5610's avatar
sas5610
Regular Visitor
3 years ago
Solved

Monthly Actual vs Target Matrix Table

Hello All,

 

What I want to Achieve:

I have a table where I consolidate all KPIs inputs from stakeholders to track their performance on monthly basis against set targets.

 

  • I want to produce a Matrix Table
  • KPIs & UOM is in the rows
  • Months and Type in the columns [two level headers] Month on Top/TGT & ACT on Bottom
  • TGT, ACT is at Alias Sort [TGT first then ACT]
  • Each KPIs monthly value listed has its own format [mentioned in notes column]
  • KPIs Driection: To apply on monthly ACT only where
  1. Increase color coding above TGT is Green [For Selected Cell ACT only]
  2. Decrease means above TGT is Red [For Selected Cell ACT only]

 

I am pretty comfortable with Power BI and producing visuals, applying custom color coding but for matrix table I am not getting the right formulas on how to enforce color coding measure on such data structure and how to enforce format per row as well since both ACT and TGT are on same column. I am specific about the visual use since its a management request 😀

 

I would apprciate your advise, direction and help in DAX if needed to be able to produce the desired output. Thanks all for your support in advance

 

Below is desired result I wish to achieve:

 

Same format applies to the rest of the months Feb-Dec

 

Facts about the Sample Data Table:

  • The data are consolidated into single table consist of 7 columns as shown below [sample data table]
  • The Date Column goes from Jan-Dec [each KPIs has a monthly actual and target value
  • Type column has TGT and ACT value type with correspondant value in value column,
  • Values Column consists of values that need to be presented in different formats

Sample Data Table

 

Show and Tell
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , sas5610 

    Thanks for your sample data first~

    According to your description, you want to create a Matrix visual as your image.

    Here are the steps you can refer to :
    (1)My test data is the same as yours.

    (2)We can create a sort column in Power Query to sort out [Type] Column:

    (3)Then we can apply the data to desktop and we need to create two measures:

    Measure = var _uon = MAX('Table'[UOM])
return
SWITCH(_uon,
"$/kg" , FORMAT(SUM('Table'[Value]) , "0.00"),
"%" , FORMAT(SUM('Table'[Value]) , "0%"),
"$k" , FORMAT(SUM('Table'[Value]) , "0.00"),
"PPM" , FORMAT(SUM('Table'[Value]) , "0"))
    Color = var _type = MAX('Table'[Type])
var _derection=MAX('Table'[KPI Direction])
var _tgt = CALCULATE( SUM('Table'[Value]) , 'Table'[Type] = "TGT",ALLEXCEPT('Table','Table'[KPI],'Table'[UOM]))
return
IF(_type="ACT",IF(OR(_derection = "Increase" && SUM('Table'[Value]) > _tgt , _derection = "Decrease" && SUM('Table'[Value]) <= _tgt )   , "green" ,"red"))

     

    (4)Then we can put the fields we need on the visual and we can configure some options we need , like this:

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , sas5610 

    Thanks for your sample data first~

    According to your description, you want to create a Matrix visual as your image.

    Here are the steps you can refer to :
    (1)My test data is the same as yours.

    (2)We can create a sort column in Power Query to sort out [Type] Column:

    (3)Then we can apply the data to desktop and we need to create two measures:

    Measure = var _uon = MAX('Table'[UOM])
return
SWITCH(_uon,
"$/kg" , FORMAT(SUM('Table'[Value]) , "0.00"),
"%" , FORMAT(SUM('Table'[Value]) , "0%"),
"$k" , FORMAT(SUM('Table'[Value]) , "0.00"),
"PPM" , FORMAT(SUM('Table'[Value]) , "0"))
    Color = var _type = MAX('Table'[Type])
var _derection=MAX('Table'[KPI Direction])
var _tgt = CALCULATE( SUM('Table'[Value]) , 'Table'[Type] = "TGT",ALLEXCEPT('Table','Table'[KPI],'Table'[UOM]))
return
IF(_type="ACT",IF(OR(_derection = "Increase" && SUM('Table'[Value]) > _tgt , _derection = "Decrease" && SUM('Table'[Value]) <= _tgt )   , "green" ,"red"))

     

    (4)Then we can put the fields we need on the visual and we can configure some options we need , like this:

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • sas5610's avatar
    sas5610
    Regular Visitor
    3 years ago

    Thanks for your support Aniya, you saved me a lot of time. Exactly what I wanted and easy to follow solution!! I have a quesiton on the color measure for clarificationn only. Why do you used max function as variable and what it does produce as output?