Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
1 | Metric1 | Weekly Actual |
2 | Metric1 | Weekly Target |
3 | Metric2 | Weekly Actual |
4 | Metric2 | Weekly Target |
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
This will give you the result below (attach PBIX file for your experimentation)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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.
1 | Metric1 | Weekly Actual |
2 | Metric1 | Weekly Target |
3 | Metric2 | Weekly Actual |
4 | Metric2 | Weekly Target |
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
This will give you the result below (attach PBIX file for your experimentation)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you very much! My table wasn't exactly set up like that, but its very easy to adjust it to that format.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |