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 =
SUMMARIZE(
** ALLSELECTED( ‘Open’ ),**
** ‘Customers’[Dealer],**
** “Total”, SUM( ‘Open’[Amount_Sum] )**
** )**
VAR _cum =
SUMX(
** FILTER( _table, [Total] >= _current ),**
** [Total] )**
VAR _cumpercent = _cum / _total
RETURN
_cumpercent
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 =
SWITCH( TRUE(),
** [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.
Table:
Group Slicer =
VAR _table =
SUMMARIZE( ‘Customers’, ‘Customers’[Dealer],
** “Group”,**
** [Group measure],**
** “Open”, [Open] )**
RETURN
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] )
RETURN
IF(
** 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 =
CALCULATE( [Open],
** FILTER( VALUES( ‘Customers’[Dealer] ),**
** COUNTROWS(**
** 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
Bram