Sorting Calculation Group within Headers

[robaranieri]

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. 

[DataInsights · Accepted solution]

@robaranieri,

Try this solution that utilizes a fields parameter. Each measure needs to be created, since a calculation group doesn't seem to allow sorting on a specific column as you indicated.

1. Create the table Topic with each topic and its associated measure names:

2. Create a fields parameter with each measure listed in the table above. Uncheck "Add slicer to this page".

3. Create a relationship between Topic and TopicParameter. The default is bidirectional, but I changed it to unidirectional (I avoid bidirectional relationships whenever possible).

4. Create a matrix and add TopicParameter to the Values field well:

5. Create a slicer using Topic[Topic].

6. Sort by any matrix column: