cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsondervorst
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
Microsoft
Microsoft

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.

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

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.