Forum Discussion
Calculation group sorting by Group & Metric
Hi There,
I've got a very specific request for PowerBi Desktop. I have the following set of dummy data:
SKU | Week | Sales | Units | Traffic |
T3G6S1 | 9/24/2022 | 100 | 50 | 1300 |
T3G6S1 | 10/1/2022 | 120 | 60 | 1500 |
X7F5G3 | 9/24/2022 | 40 | 2 | 80 |
X7F5G3 | 10/1/2022 | 20 | 1 | 90 |
PPl49G7 | 9/24/2022 | 150 | 150 | 600 |
PPl49G7 | 10/1/2022 | 300 | 300 | 200 |
I am building a tracker to see week by week how sales, units, and traffic have changed. Here is report demo link:
https://drive.google.com/file/d/1K-xL6F2Km2k-ZU9XanKZ4vbTi8b6oWCS/view?usp=sharing
I've created measures for the current week, prior week, Delta change (Δ), and percentage change (Δ %) for each metric.
The goal is to create a matrix with all the metrics with the ability to filter to a certain topic (sales, units, traffic) and with the ability to sort the table by any metric within those topics.
Without calculation groups, in Table 1 I can create the output I'd want, with the ability to sort sales Δ. However, this doesn't solve the issue of needing to filter to just sales or see side by side sales with traffic for example.
In Table 2 I can use calculation groups to create a similar output, with the ability to filter by a CalcGroup option (Sales, Units, Traffic) in order to hone in on that topic. I can even sort by a metric, but not by a metric via a topic. By that I mean I can sort by Δ, but I can't sort by Traffic Δ. I need a way to sort the matrix specifically by a specific metric within a specific topic.
Images are attached to show this issue. I've read online how to sort by a calculation group, but it does include headers or multiple metrics within the group. I've used dummy data to narrow it down to 3 topics but in actuality there are about 12 total - which is why I need a way to hone them down using CalcGroups.
Below are screenshots of the TabularEditor code and the entire dashboard.
- Anonymous3 years ago
Hi robranieri ,
You can control the visual properties using field parameters, put these measures into the field parameter, and then select the measure you want to display based on the slicer.
For more information, you can refer to the following documents:Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- robranieriRegular Visitor
Hi There,
I've got a very specific request for PowerBi Desktop. I have the following set of dummy data:
SKU
Week
Sales
Units
Traffic
T3G6S1
9/24/2022
100
50
1300
T3G6S1
10/1/2022
120
60
1500
X7F5G3
9/24/2022
40
2
80
X7F5G3
10/1/2022
20
1
90
PPl49G7
9/24/2022
150
150
600
PPl49G7
10/1/2022
300
300
200
I am building a tracker to see week by week how sales, units, and traffic have changed. Here is report demo link:
https://drive.google.com/file/d/1K-xL6F2Km2k-ZU9XanKZ4vbTi8b6oWCS/view?usp=sharingI've created measures for the current week, prior week, Delta change (Δ), and percentage change (Δ %) for each metric. The goal is to create a matrix with all the metrics with the ability to filter to a certain topic (sales, units, traffic) and with the ability to sort the table by any metric within those topics.
Without calculation groups, in Table 1 I can create the output I'd want, with the ability to sort sales Δ. However, this doesn't solve the issue of needing to filter to just sales or see side by side sales with traffic for example.
In Table 2 I can use calculation groups to create a similar output, with the ability to filter by a CalcGroup option (Sales, Units, Traffic) in order to hone in on that topic. But now I can't filter by a metric within a group.
Images are attached to show this issue. I've read online how to sort by a calculation group, but it does include headers or multiple metrics within the group.
- ShauryaMemorable Member
Hi robranieri
I suggest that you create a field parameter to the various measures that you want to show. Refer:
https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI
- AnonymousNot applicable
Hi robranieri ,
You can control the visual properties using field parameters, put these measures into the field parameter, and then select the measure you want to display based on the slicer.
For more information, you can refer to the following documents:Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.