Very slow measures - dynamic grouping customers by order amount % in total

Hi everyone,

I am encountering extremely long load times in my measures.
All output/calculation results/visuals are coming through correctly and as expected.
No change is needed in that regard, this concerns exclusively the load time / performance of the report.

There’s quiet some data in my fact/dimension tables, but I feel this has to do with the DAX setup.

I already tried to aggregate my fact table to reduce the amount of rows, but that doesn’t change much in calculation times: In the PBIX file, you will notice I worked with the ‘AggOpenbyCustomerRep’-table (8,8k rows), instead of using the unaggregated ‘Open’ table (232k rows).

What I am trying to achieve is creating a dynamic segmentation of my customers based on their open order amount (vs % in total open order amount). The output should be both by customer (left table) and by group (right table).

Open = SUMX( ‘Open’, ‘Open’[Amount_Sum] )

I accumulate these open order amount (vs % in total) on a customer-by-customer basis.

Open cum % =
VAR _total = CALCULATE( SUM( ‘Open’[Amount_Sum] ), ALLSELECTED( ‘Open’ ) )
VAR _current = SUM( ‘Open’[Amount_Sum] )
VAR _table =
** ALLSELECTED( ‘Open’ ),**
** ‘Customers’[Dealer],**
** “Total”, SUM( ‘Open’[Amount_Sum] )**
** )**
VAR _cum =
** FILTER( _table, [Total] >= _current ),**
** [Total] )**
VAR _cumpercent = _cum / _total

After getting the accumulated open order % for each customer, I created a measure to define which group (top 30% - middle 50% - bottom 20%) they belong to.

Group measure =
** [Open cum %] < 0.31, “Top”,**
** [Open cum %] > 0.8, “Bottom”,**
** “Middle”)**

One of the requirements is, that the user needs to have the ability to slice by group.
Therefore, I created below table and slicer filter. Which I used in the filter pane of the matrix.

Group Slicer =
VAR _table =
SUMMARIZE( ‘Customers’, ‘Customers’[Dealer],
** “Group”,**
** [Group measure],**
** “Open”, [Open] )**
SUMMARIZE( _table, [Group], [Open] )

Slicer filter measure, which I add in the visual filter pane (add data fields - Slicer filter is ‘1’).

Slicer Filter =
VAR _slicerfilter = SELECTEDVALUE( ‘Group Slicer’[Group] )
** ISBLANK( _slicerfilter ), 1,**
** IF( [Group measure] = _slicerfilter , 1,**
** 0 )**
** )**

I also created a ‘Groups’-table, just so the customer can see the totals by group.
To get the totals by group, I created below measures:

Open by Group =
** FILTER( VALUES( ‘Customers’[Dealer] ),**
** FILTER( ‘Groups’,**
** [Group measure] = ‘Groups’[Group]**
** )**
** ) > 0**
** )**
** )**

A final requirement is that the user needs the ability to filter the data by ‘sales org’ and material ‘hierarchy’. These slicers are included in the PBIX file. Any %'s should update dynamically (this works as well at the moment, but just very slow).

Below WeTransfer-link holds the PBIX file (75mb - was too large to upload here).

Thank you for your help!

Kind regards

1 Like

Hi @dillenbram ,

We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!