cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## PowerBI very slow to generate results from Min of Summarise table

Hi community team,

looking for some help.

I'm trying to find the count, min, max, average, median of a summarise table. It's taking very long to generate.

Summarised table consists of Customer ID, total spend, the % of total spend per customer.

Any ideas why or how to fix?

I created the following measures:

min spend =
var aTable =
SUMMARIZE(DimCustomer,DimCustomer[Id]),"rank 2",[Cumulative GMV%],"measure 3",[GMV Grid Sum])
return
MINx(aTable,[measure 3])

max spend =

var aTable =
SUMMARIZE(DimCustomer,DimCustomer[Id]),"rank 2",[Cumulative GMV%],"measure 3",[GMV Grid Sum])
return
MAXx(aTable,[measure 3])

This gives me the (incorrect) table of results, as it is the minimum total spend of all customers, shown below.

Rather I would like to acheive the minimum total spend of a customer per bucket.

Buckets is in an unconnected table in the data model.

For reference, [GMV Grid Sum]=

Cumulative GMV%=

Total GMV = Total Spend per Customer

Data Model

Kind regards

1 ACCEPTED SOLUTION
Community Support

You may create measures like DAX below.

``````min spend =

VAR aTable =

SUMMARIZE (

DimCustomer,

DimCustomer[Id],

"rank 2", [Cumulative GMV%],

"measure 3", [GMV Grid Sum]

)

RETURN

IF (

HASONEVALUE ( DimCustomer[Id] ),

[GMV Grid Sum],

MINX ( aTable, [measure 3] )

)

max spend =

VAR aTable =

SUMMARIZE (

DimCustomer,

DimCustomer[Id],

"rank 2", [Cumulative GMV%],

"measure 3", [GMV Grid Sum]

)

RETURN

IF (

HASONEVALUE ( DimCustomer[Id] ),

[GMV Grid Sum],

MAXX ( aTable, [measure 3] )

)

``````

Best Regards,

Amy

Community Support Team _ Amy

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

3 REPLIES 3
Super User

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

I've added the data model, hope this helps.

Community Support

You may create measures like DAX below.

``````min spend =

VAR aTable =

SUMMARIZE (

DimCustomer,

DimCustomer[Id],

"rank 2", [Cumulative GMV%],

"measure 3", [GMV Grid Sum]

)

RETURN

IF (

HASONEVALUE ( DimCustomer[Id] ),

[GMV Grid Sum],

MINX ( aTable, [measure 3] )

)

max spend =

VAR aTable =

SUMMARIZE (

DimCustomer,

DimCustomer[Id],

"rank 2", [Cumulative GMV%],

"measure 3", [GMV Grid Sum]

)

RETURN

IF (

HASONEVALUE ( DimCustomer[Id] ),

[GMV Grid Sum],

MAXX ( aTable, [measure 3] )

)

``````

Best Regards,

Amy

Community Support Team _ Amy

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