Data model or meaure issue causing visual has exceeded available resources error?

[laetitiaf]

Hi,

I am having an issue with after adding in credits to the data, making up about around 500k lines in total, query exceeds available resources when 10 Months+ are selected, I belive this may be due to how my data model is set up.

Performance when 12 months are selected:

Model: 
Imported through CSV

I've gone through and changed all numeric data types to fixed decimal (having 16 digit values) - This helped take it from 4 months to 9 months that can be loaded

example of data from sales/credits

Code	Customer Name	Reference	Customer Ref	Type	Stock Code	Customer Type	Description	Date	QTY	Cost Value	Selling Price	Discount	Tax(GST)	Total
AMY	AMY HALLY	254391	PO239441	drinv	1687	F2	DVD Series 4	17-12-2024	4	78.04	23.51	0	9.4	103.44
JON	JON DOE	254101		drinv	1687	KD	DVD Series 4	13-12-2024	1	19.51	22.33	0	2.23	24.56
JON	JON DOE	253395		drinv	1687	KD	DVD Series 4	09-12-2024	1	19.51	22.33	0	2.23	24.56

Measures:

Sales =

SUMX(

    'Invoices',

    [Quantity] * [Selling Price]

) +

SUMX(

    'Credits',

    [Quantity] * [Selling Price]

) +

SUMX(

    'Invoices', 'Invoices'[Discount]

) +

SUMX(

    'Credits', 'Credits'[Discount]

)

Sales Growth =

DIVIDE(([Sales]-[Sales Last Year]),[Sales Last Year])

GP =

CALCULATE(

        ([Sales] - [Cost]),

        FILTER('Invoices', 'Invoices'[Transaction Type] = "drinv"))

Margin =

DIVIDE([GP],[Sales])

[johnt75 · Accepted solution]

You can halve the number of iterations needed by adding the discounts during the first iterations

Sales =
SUMX (
    'Invoices',
    ( 'Invoices'[Quantity] * 'Invoices'[Selling Price] ) + 'Invoices'[Discount]
)
    + SUMX (
        'Credits',
        ( 'Credits'[Quantity] * 'Credits'[Selling Price] ) + 'Credits'[Discount]
    )

Also, change GP to

GP =
CALCULATE ( ( [Sales] - [Cost] ), 'Invoices'[Transaction Type] = "drinv" )

Never filter entire tables if you can avoid it, filter columns instead. Its much safer and much quicker.

[laetitiaf]

Absolute legend, apreciate the explaination for the changes, that works perfectly, thank you so much!