Dynamic Ordering of Calculation Group Items (different order based on visual)

[Mikee_13]

Hi Community Team,

I've been Googling this issue for weeks and have been unable to find a workable solution and hoping someone within his community can provide a solution to this issue:

I have a report using a Calculation Group that has the following calculation items which is used to calculate Previous Year, Forecast, Budget and Actuals for each measure:

I have created several matrix. The first shows a total summary of each calculation item for all measure types, as below:

Summary View:

Columns = Calculation Items

Rows = Measures

I then have other views that are used for comparison (for example by country, product group, period), as per below example:

Comparison View:

The issue I have is that in the summary view the management insist on having the order of the calculation items showing as : PY, BUD, FRCST, ACT. But in the comparison view it should have ACT first and then the item it is being compared to next, so in above example 'ACT:  should show first and then 'PY' as second.

I have tried so many different options but cannot find an effective way to have the different ordering of the calculation items based on the visual or a measure selection. The current solution is to take a snapshot of the summary visual for the PPT, then go into the model view, adjust the ordering and take the snapshot of the comparison visual which is far from ideal and also means this report can not be published and managed in Service.

I feel as though there should be a simple solution to this. I find it very frustrating that whenever trying to convert reporting into Power BI that I always run into simple requests from management that seem to then be impossible to achieve or end up in hours of research 😞

I would be very grateful if someone could provide an effective solution to this as I am even losing sleep at the moment as my brain can't stop thinking about this issue.

Regards

Mike

[OwenAuger · Accepted solution]

Hi @Mikee_13 

To allow for different orderings of calculation items, you can add additional copies of the calculation item column as well as additional Ordinal columns to the calculation group, along with a few setup steps described below.

This is an extension of the method covered in this blog post.

Hopefully this is the sort of thing you are looking for 🙂

I have attached a simple example PBIX with a model set up in this way with a Time Intelligence calculation group.

The steps I used to set this up:

1. Created the original calculation group called Time Intelligence with calculation item column Time Calc.
2. Added a calculated column Time Calc Alternate which is just a copy of the original calculation item column:
   
   Time Calc Alternate ='Time Intelligence'[Time Calc]
3. Added a calculated column Ordinal Alternate containing the alternate ordering of calculation items. I used a SWITCH expression to set the value based on Time Calc value.
4. Set Time Calc Alternate to sort by Ordinal Alternate.
5. Critical step: Set Time Calc Alternate's Group By Columns property to Time Calc.
   See this article for detail on setting this property using Tabular Editor.

After following these steps, you can place either Time Calc or Time Calc Alternate in a visual and they will function correctly as calculation items and sort as specified by the Ordinal or Ordinal Alternate columns.

Field parameter to select between calculation group columns

• You can also create a Field Parameter to allow selection between the two orderings by the report consumer, by selecting between Time Calc or Time Calc Alternate.
• In the sample PBIX, I added a slicer to the page for this Field Parameter, but this could be applied via visual-level filter or could be tied to some dynamic logic (see this blog post).

I wouldn't call this a simple solution by any stretch, especially as the Group By Columns property is not accessible in the Power BI Desktop interface! But at least it works and is relatively straightforward to maintain.

Hopefully this is useful, and please post back with any further questions 🙂

[Mikee_13]

@OwenAuger

I've got Tabular Editor installed and the solution you provided has worked perfectly. Also the links you supplied were interesting reading too so thank you very much 🙂

I would like to check one more thing if you wouldnt mind.

I have created a measure that applies filtering on the visual so that the selected value from the parameter used for sorting the calculation items also filters the calculation items automatically to only the items related to that view. This is working although it is quite slow so I may be using a poor solution to perform this dynamic filtering. If you get a chance I would be grateful if you could look through my below solution for this to see if it could be improved?

• Field Parameter 'Scope Views' has been created with the following columns from the calculation group: 'Summary', 'Act vs Bud', 'Act vs Frcst', 'Act vs PY'
• I have then created the below measure that returns a boolean value and is applied to the filter in the visual to only show where it returns value 1:

ScopeFilter_CG_Items = 
Var SummaryView = {"PY", "BUD", "FRCST", "ACT", "ACT vs FRCST", "ACT vs FRCST %", "ACT vs BUD", "ACT vs BUD %", "ACT vs PY", "ACT vs PY %"}
Var ActVsBudView = {"ACT", "BUD", "ACT vs BUD %"}
Var ActVsFrcstView = {"ACT", "FRCST", "ACT vs FRCST %"}
Var ActVsPYView = {"ACT", "PY", "ACT vs PY %"}
Var SelectedView_Param = MAX('Scope Views'[Scope Views]) // Field Parameter for Calculation Group Columns
Var CurrentCalcItem = SELECTEDVALUE('_CG_Scope Figures'[Summary])
Var result = 
    SWITCH(SelectedView_Param,
        "Summary", INT(CurrentCalcItem IN SummaryView),
        "Act vs Bud", INT(CurrentCalcItem IN ActVsBudView),
        "Act vs Frcst", INT(CurrentCalcItem IN ActVsFrcstView),
        "Act vs PY", INT(CurrentCalcItem IN ActVsPYView),
        INT(CurrentCalcItem IN SummaryView)
    )
Return
    result

This all works nicely and filters my visual with the desired calculation items and they are sorted correctly as per the solution you provided above. It is quite slow and my feeling is that the speed reduced when I introduced the above filtering measure:

 

Regards

Mike

[OwenAuger · Accepted solution]

Hi again @Mikee_13 ,

Glad the solution for controlling the calculation item sort order worked! 🙂

On the question of filtering calculation items based on the "View" selection, your current measure-based filter might be suffering from too many computations on-the-fly.

Here is one method I tried that seems to perform acceptably with some test data:

PBIX: Calculation item order v2.pbix

1. Create a table View Time Calc that contains the required combinations of View and Calculation Item which is then related many-to-one to the field parameter table (View in my example).

To help create this, I first ensured that the Ordinal columns in the 'Time Intelligence' calculation group are blank for items that should be hidden:

'Time Intelligence' table:

View Time Calc table:

View Time Calc = 
GENERATE (
    SELECTCOLUMNS ( 'View', "View", 'View'[View] ),
    VAR CurrentView = [View]
    RETURN
        CALCULATETABLE (
            VALUES ( 'Time Intelligence'[Time Calc] ),
            SWITCH (
                CurrentView,
                "Summary", NOT ISBLANK ( 'Time Intelligence'[Ordinal Summary] ),
                "Act vs Bud", NOT ISBLANK ( 'Time Intelligence'[Ordinal ActVsBud] ),
                "Act vs Frcst", NOT ISBLANK ( 'Time Intelligence'[Ordinal ActVsFrcst] ),
                "Act vs PY", NOT ISBLANK ( 'Time Intelligence'[Ordinal ActVsPY] )
            )
        )
)

Relationships

2. Create a second calculation group called Filter Time Intelligence that blanks out measures when the calc item is not in the set of calc items for that View. It has a single calculation item "Filter Time Calc by View":

IF (
    SELECTEDVALUE ( 'Time Intelligence'[Time Calc] ) IN VALUES ( 'View Time Calc'[Time Calc] ),
    SELECTEDMEASURE ( )
)

3. Lastly apply 'Filter Time Intelligence'[Filter] = "Filter Time Calc by View" as a filter on the required visuals:

Alternatives:

• You could try a blend of your original method using a measure-baesd filter and this method with a table of allowed calculation items.
• Another method I didn't mention (since I don't really encourage it) is simply to create additional copies of each calculation item with a grouping column (View in this case) added to the calculation group. This can be a maintenance nightmare since multiple copies of each calculation item need to be maintained.

I'd be interested in whether the above method improves performance with your model. 🙂

[Mikee_13]

Hi Owen,

Amazing, you are some kind of DAX wizard 🙂

The above solution has worked for me and performance has improved considerably. I did make a change to this solution where I am using a table created in Excel to list the calculation items that should be displayed for each 'View' as I will find this easier to maintain (although the DAX created table is something I would like to study more as very interested in how that all works).

I was originally thinking along these lines with my solution but couldn't quite figure out how to get it all working with a table.

Thank you again for all your help with this.

Regards

Mike

[Mikee_13]

Hi Owen,

Thank you for the detailed reply and it looks like exactly what I am after. I need to await approval to get Tabular Editor to try this out but will let you know the results 🙂

[OwenAuger · Accepted solution]

Hi @Mikee_13 

To allow for different orderings of calculation items, you can add additional copies of the calculation item column as well as additional Ordinal columns to the calculation group, along with a few setup steps described below.

This is an extension of the method covered in this blog post.

Hopefully this is the sort of thing you are looking for 🙂

I have attached a simple example PBIX with a model set up in this way with a Time Intelligence calculation group.

The steps I used to set this up:

1. Created the original calculation group called Time Intelligence with calculation item column Time Calc.
2. Added a calculated column Time Calc Alternate which is just a copy of the original calculation item column:
   
   Time Calc Alternate ='Time Intelligence'[Time Calc]
3. Added a calculated column Ordinal Alternate containing the alternate ordering of calculation items. I used a SWITCH expression to set the value based on Time Calc value.
4. Set Time Calc Alternate to sort by Ordinal Alternate.
5. Critical step: Set Time Calc Alternate's Group By Columns property to Time Calc.
   See this article for detail on setting this property using Tabular Editor.

After following these steps, you can place either Time Calc or Time Calc Alternate in a visual and they will function correctly as calculation items and sort as specified by the Ordinal or Ordinal Alternate columns.

Field parameter to select between calculation group columns

• You can also create a Field Parameter to allow selection between the two orderings by the report consumer, by selecting between Time Calc or Time Calc Alternate.
• In the sample PBIX, I added a slicer to the page for this Field Parameter, but this could be applied via visual-level filter or could be tied to some dynamic logic (see this blog post).

I wouldn't call this a simple solution by any stretch, especially as the Group By Columns property is not accessible in the Power BI Desktop interface! But at least it works and is relatively straightforward to maintain.

Hopefully this is useful, and please post back with any further questions 🙂