Forum Discussion

laetitiaf's avatar
laetitiaf
Frequent Visitor
1 year ago
Solved

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

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

CodeCustomer NameReferenceCustomer RefTypeStock CodeCustomer TypeDescriptionDateQTYCost ValueSelling PriceDiscountTax(GST)Total
AMYAMY HALLY254391PO239441drinv1687F2DVD Series 417-12-2024478.0423.5109.4103.44
JONJON DOE254101 drinv1687KDDVD Series 413-12-2024119.5122.3302.2324.56
JONJON DOE253395 drinv1687KDDVD Series 409-12-2024119.5122.3302.2324.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])
 
 
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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.

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
      laetitiaf
      Frequent Visitor
      1 year ago

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