Format by Row

[nzcatala]

I know that PowerBi is column based, but let me explain what I'm looking for and maybe somebody knows a way that it can be done.

Currently, my department tracks a lot of different metrics, and printing them off on paper is becoming wasteful due to the amount of metrics and power point slides, so I'm attempting to bring it over to PowerBI.  For each individual metric, there are no issues, but making a "summary" page that looks like the old one is proving to be difficult.

What I'm looking for:

Week                      1   2   3   4   ...

Metric 1

    Weekly Actual     1   0   1   0   ....

    Weekly Target     .5  .5  .5  .5  .....

Metric 2

    Weekly Actual     60% 65% 40% 70%

    Weekly Target     50% 50% 50% 50%

...

The matrix visual builds this pretty much perfectly, but I am unable to format based on the corresponding metric.  For example, metric 1 is a whole number and metric 2 is a percentage.

Let me know your thoughts! 

[MFelix · Accepted solution]

Hi @nzcatala,

I'm assuming your table looks something like this:

Week         Metric          Target            Value

1	Metric1	Weekly Actual	1
1	Metric1	Weekly Target	0,5
1	Metric 2	Weekly Actual	0,6
1	Metric 2	Weekly Target	0,5
2	Metric1	Weekly Actual	1
2	Metric1	Weekly Target	0,5
2	Metric 2	Weekly Actual	0,65
2	Metric 2	Weekly Target	0,5
3	Metric1	Weekly Actual	1
3	Metric1	Weekly Target	0,5
3	Metric 2	Weekly Actual	0,4
3	Metric 2	Weekly Target	0,5
4	Metric1	Weekly Actual	1
4	Metric1	Weekly Target	0,5
4	Metric 2	Weekly Actual	0,7
4	Metric 2	Weekly Target	0,5

You need to do several steps in order to create what you need since the values are on a single column and the format is made by column.

1. Create a measure for every Metrics Calculation you need in this case 4 measures will be needed

2. Metric 1 measure Actuals =
   CALCULATE (
       SUM ( Metrics[Value] );
       Metrics[Metric] = "Metric1";
       Metrics[Type] = "Weekly Actual"
   )
   
   Metric 1 measure Target =
   CALCULATE (
       SUM ( Metrics[Value] );
       Metrics[Metric] = "Metric1";
       Metrics[Type] = "Weekly Target"
   )
   
   Metric 2 measure actuals =
   CALCULATE (
       SUM ( Metrics[Value] );
       Metrics[Metric] = "Metric 2";
       Metrics[Type] = "Weekly Actual"
   )
   
   
   Metric 2 measure Target =
   CALCULATE (
       SUM ( Metrics[Value] );
       Metrics[Metric] = "Metric 2";
       Metrics[Type] = "Weekly Target"
   )

   This measures will be used to fill the matrix with the formatting you need.
3. Create a table with the following format:
4. ID           Metrics         Target

   1	Metric1	Weekly Actual
   2	Metric1	Weekly Target
   3	Metric2	Weekly Actual
   4	Metric2	Weekly Target

5. Create the following 2 measures:

6. Measure_Select = MAX('Metrics Measures'[ID])
   
   
   Metrics_Matrix =
   SWITCH (
       [Measure_Select];
       1; [Metric 1 measure Actuals];
       2; [Metric 1 measure Target];
       3; FORMAT ( [Metric 2 measure actuals]; "###.00%" );
       4; FORMAT ( [Metric 2 measure Target]; "###.00%" )
   )

   As you can see the last measure (Metrics_Matrix) is where the formatting is done
7. Then just add on the matrix the following fields
   1. Rows: Metrics Measures[Metrics]
   2. Rows: Metrics Measures[Target]
   3. Columns: Metrics[Week] (column from your orignal data)
   4. Values: [Metrics_Matrix]

This will give you the result below (attach PBIX file for your experimentation)

Regards,

MFelix

[nzcatala]

Thank you very much!  My table wasn't exactly set up like that, but its very easy to adjust it to that format.