Sales Revenue Segmentation Drill Through

[Swish11]

I am working with a composite model that is providing sales information at an aggregated level which the business would like to drill through to see detailed information. The aggregate sums sales by the parent client to place the entire "client" in a segment bucket. The drill through needs to show the individual clients that make up the parent totals.

In this snapshot, let's focus on the >200k segment. You can see that when sales is summed by parent code, there is $5.4M in sales and 8 parent clients. 

Total Sales =
TRUNC(CALCULATE(
[Adjusted Sales],
FILTER(
VALUES( 'Accounts'[Parent Client Code] ),
COUNTROWS(
FILTER( 'Revenue Segments',
AND(
[Adjusted Sales Revenue] >='Revenue Segments'[Start],
[Adjusted Sales Revenue] <='Revenue Segments'[End]
) )
) > 0
) ) )

Total Client Count Segment_C =
CALCULATE(
[Total Parent Code Count],
FILTER(
VALUES('Accounts'[Parent Client Code] ),
COUNTROWS(
FILTER( 'Revenue Segments',
AND(
[Adjusted Sales Revenue] >='Revenue Segments'[Start],
[Adjusted Sales Revenue] <='Revenue Segments'[End]
))) > 0
))

[Adjusted Sales Revenue] is a measure from my tabular model.

The numbers on the matrix are verified. The conundrum I now face is when I drill through to the detail, I am missing records. The matrix is applying the >200k segment to each child row, which drops two of the eight clients because the child clients do not have <200k in sales.

When the drill through happens, I need the segment row filter >200k dropped so all children with parents that are counted in the eight total records are displayed.

Missing records:

ParentID	ClientID	SalesRevenue
474036	1627244	43,225
474036	1627244	166,542
292044	2565362	88,535
292044	2565362	195,107

I am unable to share the pbix file due to the sensitive nature of the information.

[Swish11 · Accepted solution]

I ended up having to hard-code the segments in the model for this to work. Power BI would not retain the correct filter context to allow for drill through.

[v-yangliu-msft]

Hi  @Swish11 ,

According to the official Microsoft article description：

Report interactions will execute DAX queries. Each DAX query consumes a certain amount of temporary memory required to produce the results. Each query may consume a different amount of memory and will be subject to the query memory limitation as described.

Configure workloads

Maximize your capacity's available resources by enabling workloads only if they will be used. Change memory and other settings only when you have determined default settings are not meeting your capacity resource requirements.

To configure workloads in the Power BI admin portal

1. In Capacity settings > PREMIUM CAPACITIES, select a capacity.
2. Under MORE OPTIONS, expand Workloads.
3. Enable one or more workloads, and set a value for Max Memory and other settings.
4. Select Apply.

Refer:https://blog.crossjoin.co.uk/2020/01/20/visual-has-exceeded-the-available-resources-error-power-bi/ 

You can try to change the function to this form:

Add the all() function:

Total Client Count Segment_C =
CALCULATE(
[Total Parent Code Count],
FILTER(
VALUES('Accounts'[Parent Client Code] ),
COUNTROWS(
FILTER( ALL('Revenue Segments'),
AND(
[Adjusted Sales Revenue] >='Revenue Segments'[Start],
[Adjusted Sales Revenue] <='Revenue Segments'[End]
))) > 0
))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Swish11]

Unfortunately, using ALL('Revenue Segments') returns the total sales on all rows of the matrix, ignoring the start and end requirements for the segment value.

[amitchandak]

@Swish11 , Change the filter measure like

CALCULATE(
[Total Parent Code Count],
FILTER(
VALUES('Accounts'[Parent Client Code] ),
calculate(COUNTROWS(
FILTER( 'Revenue Segments',
AND(
[Adjusted Sales Revenue] >='Revenue Segments'[Start],
[Adjusted Sales Revenue] <='Revenue Segments'[End]
))) , filter(allselected('Accounts') , 'Accounts'[Parent Client Code] = max('Accounts'[Parent Client Code]))) > 0
))

[Swish11]

I made this modification and am now getting this error message.