Forum Discussion

robranieri's avatar
robranieri
Regular Visitor
3 years ago
Solved

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. 

 

 

 

3 Replies

  • robranieri's avatar
    robranieri
    Regular 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=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.

     

  • Anonymous's avatar
    Anonymous
    Not 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.