Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Performance issue on calculations

Hi,

 

I am wondering something about data model design to improve performance.

I actually load data from multiple tables (financial data). The data is P&L lines for countries by month, business, scenario, year and other semgentations. Each table containing one scenario in one currency (EUR / Local Currency).

 

I load each table seperatly and then append all the queries into on big data table of 500k lines.

Now I'm computing some effects (Volume, price, mix range, mix countries, rate) and those calculations are quite complex because of exceptions. Those calculations are very slow and I'm wondering how I could improve this (Force to use sumx(summarize()) for many of the effects)

Would it be faster to keep each scenario in a different table instead of using one big data table ?

 

I'll put the example of the mix country formula. We have many exceptions to take into account which makes the calculation complex :

 

MixCtry_CA = SUMX(SUMMARIZE(DATA;COUNTRIES[_CTRY LVL_BRIDGE];BRAND[RANGE]);IF([HasMultiBus];[NetSales_A18_EUR]-[NetSales_SCN_EUR]-[Vol_CA]-[Price_CA]-[MixRange_CA]-[Rate_CA];SUMX(SUMMARIZE(DATA;BUSINESS[TOT BUS]);IF(OR([Units_A18]=0;[Units_SCN]=0);[NetSales_A18_EUR]-[NetSales_SCN_EUR]-[Vol_CA]-[Price_CA]-[MixRange_CA]-[Rate_CA];IFERROR(([Units_A18]-[Units_SCN])*(CALCULATE(DIVIDE([NetSales_A18_LC]/[Rate_SCN];[Units_A18];0);ALLEXCEPT(BRAND;BRAND[BRAND]))-[Price_AVG_A18]);0)))))

This calculation handles two exceptions : The first one is the multiple business cases on one of the scenario, note that the TOT BUS is not present on the first SUMMARIZE. The second exception is 0 units on one the scenario. The real effect calculation starts with IFERROR.

 

Thanks

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi,@Lichar

      After my research, keep each scenario in a different table is faster than using one big data table.

It involves Vertipaq Engine and data storage mode

Reference:https://www.microsoftpressstore.com/articles/article.aspx?p=2449192

 

and some of the tips and techniques to improve the calculation 

http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

 

Best Regards,

Lin

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

Hi !

 

Thanks for your answer.

 

I change my data set so as to keep each scenario in a different table but didn't think of the measure computation before.

 

Indeed how can I use a SUMMARIZE on different tables ?

 

For example in each table I have multiple countries that can have 4 businesses. I use in the effects an "average price" which is computed per group of business (I have 2 groups, each one taking 2 businesses). Before I was using 

SUMX(SUMMARIZE(DATA;BUSINESS[TOT BUS]);price_calculation

to get the desire result but now I am struggling to find how to achieve this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.