Calculation group sorting by Group & Metric

[robranieri]

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. 

[robranieri]

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. 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.

[Shaurya]

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

[Anonymous · Accepted solution]

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.