cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsondervorst Frequent Visitor
Frequent Visitor

Couldn't load the data for this visual - Sumx

Hello,

 

I'm making a report with sales figures for different regions.

At first, I created a measure for a month-to-date total.

TOTALMTD(SUM('salestbl'[Value]), 'Date'[Date])

However, the totals weren't adding up correctly. So after some digging, I found following formula:

TOTALMTD(SUMX(IMS, CALCULATE(SUM('IMS'[Value]))), 'Date'[Date])

This seemed to work for the totals as well.

Unfortunately, when I try to make a table with combined sales for all regions (full country), I get the error 'Couldn't load the data for this visual'. On a filtered dataset however, it works fine. I've read that this may be due to the SUMX part, as it evaluates every single row.

Is there a way to optimise this measure so it won't take so much memory so I can report on the full dataset giving the possibility to see the country total next to the region total (in another table)?

 

Thank you in advance for any help.

 

3 REPLIES 3
Microsoft v-cherch-msft
Microsoft

Re: Couldn't load the data for this visual - Sumx

Hi @jsondervorst

 

You may try to check which dimension should be used in the table. And the relationships between your tables.If it is not your case, please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. 

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
jsondervorst Frequent Visitor
Frequent Visitor

Re: Couldn't load the data for this visual - Sumx

Hi @v-cherch-msft,

 

Below image shows the problem with the use of the first SUM formula:

issue.JPG

 

As you can see, the total doesn't equal the sum of the subtotals (should be 76781) for BCX MTD.

So correcting this with the SUMX formula, resolves this problem, but leads to error 'Visual has exceeded the available resources' in the service for unfiltered data.

 

Regards,

Jonne

Microsoft v-cherch-msft
Microsoft

Re: Couldn't load the data for this visual - Sumx

Hi @jsondervorst

 

You may try to use IF Function to change the total in your visual. Here are the references for you:

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

 https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M...

 

If it is not your case, please share the data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)