cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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!:
Mastering Power BI 2nd Edition

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors