Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello. I need to create a daily summary chart that reports the total, max, average and max / average of several measures.
For example...
Source Data:
A | B |
2 | 10 |
5 | 20 |
6 | 50 |
3 | 10 |
Desired result in Power BI
A | B | |
Total | 16 | 90 |
Max | 6 | 50 |
Avg | 4 | 22.5 |
Max : Avg | 1.5 | 2.22 |
I previously acheived this result by creating a view in SQL Server to calculate the dataset that I needed and threw it into a matrix using a column called "function" for the row header. But I'm rebuilding the report to more properly utilize measures.
I don't think it is possible to create a visual that would accept a measure and then automatically apply these sort of aggregate functions. (Perhaps it would be possible with columns instead of measures, but that wouldn't help in this situation.)
Is there a visual that would allow me to arbitrarily put measures into a matrix, somewhat like Excel does? Something that would allow me build a measure for calculating the max of A and then displaying it in (1,1) and then putting other measures into other cell positions?
Or, would is there a better visualization to display these values? Ultimately, Max : Avg is the most important measure. This table has 6 measures / column headers.
I'm open to any thoughts or ideas 😃 Thank you!
Solved! Go to Solution.
Hi @alexeisenhart ,
By my test, I can’t put the measure to a matrix using your sample data. The following is the sample that I implement in Power Query. If there are other columns and relationships in your actual tables, you can reference the blog to have a try using the option of Show on rows .
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @alexeisenhart ,
By my test, I can’t put the measure to a matrix using your sample data. The following is the sample that I implement in Power Query. If there are other columns and relationships in your actual tables, you can reference the blog to have a try using the option of Show on rows .
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
97 | |
97 | |
73 | |
72 |