Forum Discussion
Dynamic calculation based in two different tables
Whist not ideal you can reshap your xlsx and csv data using Query Editor/M into a better model.
That's one of great features of powerbi you can improve source data structure as part of the refresh.
" ... Don't hold year summaries on these rows. ... "??
If have rows at monthly level/grain don't have a columns that has the current/prior yearly total. Mixing data can make your life harder as you've got to make sure your not double counting data.
You can either aggregate the detail values or create a summary table at year level/grain.
There are lots of posts of time intelligence here from the SQLBI boys
https://www.sqlbi.com/topics/time-intelligence/
" ... I would also have a row for each currency so that it's easy to filter. ... "
You have BRL and EUR values on the same rows as different columns for a number of values.
If you create a row for each Currency with a Currency code on the row you can filter and work with the values based on the selected currency. It should make your calcs simpler as they can be the same as you'll only have a single name for each money column and not need to do so many conditions. You just need to ensure you always only working on a single currency. If your main currency is BRL and you create the EUR from the exchange rate you could create a 2nd version of the detail data in Query Editor/M and append the data together Adding a new column for the Currency Code or each of the data sets.
Powerbi and Tabular models work better with long lists rather than lots of columns.
Ruth ruthpozuelo at Curbal has some good videos on lots of topics. The 3 Vertipaq ones help you understand how the powerbi works so you can make it work better.
https://www.youtube.com/watch?v=b8oSgTOeYCE
Also take a look at this about comparison vs budgets and handling data at different grains also from the SQLBI boys.
https://www.daxpatterns.com/budget-patterns/
I think the problem is down to precision. with Pilot Base 4 I see the PilotImpact EUR as 5.82672036%.
You can round the figure to get a match with the manual 5.83
Based LY Realized Obj Bil _PilotEvoImpact EUR ### WRONG ### =
CALCULATE (
SUM ( MiddleOffice[BILLING EUR] ),
ALL ( 'Date'[Year] ),
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
- CALCULATE (
SUM ( MiddleOffice[BILLING EUR] ),
ALL ( 'Date'[Year] ),
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
* ROUND([PilotImpact EUR],4)
Hi stretcharm, I have applied as you indicated, but unfortunatelly it failed. See picture with final result. I simply cannot understand why this is happening.
Failed
It looks like your mixing the grain of your data.
Evolution % or PilotImpact is being calculated at month level given a different value when you group by month and at year for the total which is correct.
Can I suggest you take a look at your model.
The calculations are pretty slow and the model seems very complex.
Take a look at this webinar by avisingh it's a good overview of powerbi modelling.
https://www.youtube.com/watch?v=pvIVMEFQokE
I would try and keep data at the lowest level you need e.g month or day. Don't hold year summaries on these rows.
You can then summarise in DAX.
I would also have a row for each currency so that it's easy to filter.
It's possible to create a DAX summary tables if you want data to work at a different level.
e.g.
PilotSummary = ADDCOLUMNS ( SUMMARIZE ( Pilot, Pilot[YEAR], Pilot[PILOT BASE], Pilot[FINAL PILOT BASE] ), "SELL_BILLINGS_Year", SUM ( Pilot[SELL_BILLINGS] ) )Also work checking this video from AlbertoFerrari about optimising Dax. Some good tips here for slow calcs and model design.
https://www.sqlbi.com/tv/optimizing-analyzing-dax-query-plans-sqlbits-xii/
Hi stretcharm, thanks again for your great support.
Yes, I agree this Data model is too complex, but the problem (besides many slow DAX calc I have created) is that company I work for has such a complexity and worse all my DimTables and FactTables are coming from .xlsx and csv. It is not possible to change this structure at least for now doing ETL in another layer (eg SQL) or other place. Also because of this natural Business complexity I do need many DimTables as you could see. Complicated :(
Eventhough I will follow your suggestions: looking to my data model and consuming links you shared.
Moreover I will try to reproduce a DAX summary table as suggested.
Now, if you have time, could you please explain with more details as per below (if it is possible to give further details) because I didn't get it.
" ... Don't hold year summaries on these rows. ... "??
" ... I would also have a row for each currency so that it's easy to filter. ... "
Whist not ideal you can reshap your xlsx and csv data using Query Editor/M into a better model.
That's one of great features of powerbi you can improve source data structure as part of the refresh.
" ... Don't hold year summaries on these rows. ... "??
If have rows at monthly level/grain don't have a columns that has the current/prior yearly total. Mixing data can make your life harder as you've got to make sure your not double counting data.
You can either aggregate the detail values or create a summary table at year level/grain.
There are lots of posts of time intelligence here from the SQLBI boys
https://www.sqlbi.com/topics/time-intelligence/
" ... I would also have a row for each currency so that it's easy to filter. ... "
You have BRL and EUR values on the same rows as different columns for a number of values.
If you create a row for each Currency with a Currency code on the row you can filter and work with the values based on the selected currency. It should make your calcs simpler as they can be the same as you'll only have a single name for each money column and not need to do so many conditions. You just need to ensure you always only working on a single currency. If your main currency is BRL and you create the EUR from the exchange rate you could create a 2nd version of the detail data in Query Editor/M and append the data together Adding a new column for the Currency Code or each of the data sets.
Powerbi and Tabular models work better with long lists rather than lots of columns.
Ruth ruthpozuelo at Curbal has some good videos on lots of topics. The 3 Vertipaq ones help you understand how the powerbi works so you can make it work better.
https://www.youtube.com/watch?v=b8oSgTOeYCE
Also take a look at this about comparison vs budgets and handling data at different grains also from the SQLBI boys.
https://www.daxpatterns.com/budget-patterns/