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
v-cherch-msft Super Contributor
Super Contributor

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.
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

v-cherch-msft Super Contributor
Super Contributor

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 25 members 901 guests
Please welcome our newest community members: