Couldn't load the data for this visual - Sumx

[jsondervorst]

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.

[v-cherch-msft]

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

[jsondervorst]

Hi @v-cherch-msft,

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

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]

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