Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello again community.
I only come here when I have tried all I know and also tried to search some solution in many places. Sometimes we have a very specific challenge that needs special and focused help. Or maybe the solution is so simple, but I am blind and complicating the situation.
What I need?: Define this year annual Billing target based on an evolution (last year over projection of this year) result from another table. If you prefer, I am sharing in my One Drive the .pbix file called "SampleData_ModifiedNumbers" + a simple print with desired outcome. Link to sample data
Inside dashbord I have Measure items A, B, C, D and E and wrong / correct outcome:
Basically I want to subtract Item D from Item E to get this year target, but I need to have this automatically according to Year and Pilot Base slicers. So, if you select 2018 and 4 we have an Evolution of 5,83%. If you select 2018 and 3 now we have 5,89% and so on.
*** DAX measures:
Item A:
Billing Pilot Last Year = VAR BRL = IF ( HASONEVALUE ( 'Date'[Year] ); CALCULATE ( SUM ( Pilot[SELL_BILLINGS_LCY] ); ALL ( Pilot[PILOT BASE] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ); Pilot[FINAL PILOT BASE] = "YES" ); BLANK () ) VAR EUR = IF ( HASONEVALUE ( 'Date'[Year] ); CALCULATE ( SUM ( Pilot[SELL_BILLINGS_LCY_EUR] ); ALL ( Pilot[PILOT BASE] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ); Pilot[FINAL PILOT BASE] = "YES" ); BLANK () ) RETURN IF ( HASONEVALUE ( 'Currency'[Currency] ); SWITCH ( VALUES ( 'Currency'[Currency] ); "BRL"; BRL; "EUR"; EUR; BRL ); BRL )
Item B:
Billing Pilot = IF ( HASONEVALUE ( 'Currency'[Currency] ); SWITCH ( VALUES ( 'Currency'[Currency] ); "BRL"; [Billing Pilot _BRL]; "EUR"; [Billing Pilot _EUR] ); [Billing Pilot _BRL] ) Sub DAXs: Billing Pilot _BRL = SUM( Pilot[SELL_BILLINGS_LCY]) Billing Pilot _EUR = SUM( Pilot[SELL_BILLINGS_LCY_EUR])
Item C:
Billing Pilot Evolution ABS = IF ( HASONEVALUE ( 'Currency'[Currency] ); SWITCH ( VALUES ( 'Currency'[Currency] ); "BRL"; [Billing Pilot Evolution ABS BRL]; "EUR"; [Billing Pilot Evolution ABS EUR] ); [Based 2017 Global Obj Bil _PilotEvoImpact BRL] ) Sub DAXs: Billing Pilot Evolution ABS BRL = [Billing Pilot _BRL] - [Billing Pilot Last Year _BRL] Billing Pilot Evolution ABS EUR = [Billing Pilot _EUR] - [Billing Pilot Last Year _EUR]
Item D:
Billing Pilot Evolution % = IF ( HASONEVALUE ( 'Currency'[Currency] ); SWITCH ( VALUES ( 'Currency'[Currency] ); "BRL"; [Billing Pilot Evolution % BRL]; "EUR"; [Billing Pilot Evolution % EUR] ); [Billing Pilot Evolution % BRL] ) Sub DAXs: Billing Pilot Evolution % BRL = DIVIDE([Billing Pilot _BRL] - [Billing Pilot Last Year _BRL]; [Billing Pilot Last Year _BRL];0) Billing Pilot Evolution % EUR = DIVIDE([Billing Pilot _EUR] - [Billing Pilot Last Year _EUR]; [Billing Pilot Last Year _EUR];0)
Item E:
Based LY Realized Act Bil = IF ( HASONEVALUE ( 'Currency'[Currency] ); SWITCH ( VALUES ( 'Currency'[Currency] ); "BRL"; [Based LY Realized Act Bil BRL]; "EUR"; [Based LY Realized Act Bil EUR]; [Based LY Realized Act Bil BRL] ); [Based LY Realized Act Bil BRL] ) Sub DAXs:
Based LY Realized Act Bil BRL = CALCULATE ( SUM ( MiddleOffice[BILLING BRL] ); ALL ( 'Date'[Year] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) Based LY Realized Act Bil EUR = CALCULATE ( SUM ( MiddleOffice[BILLING EUR] ); ALL ( 'Date'[Year] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
WRONG Outcome:
Based LY Realized Obj Bil _PilotEvoImpact ### WRONG ### = IF ( HASONEVALUE ( 'Currency'[Currency] ); SWITCH ( VALUES ( 'Currency'[Currency] ); "BRL"; [Based LY Realized Obj Bil _PilotEvoImpact BRL ### WRONG ###]; "EUR"; [Based LY Realized Obj Bil _PilotEvoImpact EUR ### WRONG ###]; [Based LY Realized Obj Bil _PilotEvoImpact BRL ### WRONG ###] ); [Based LY Realized Obj Bil _PilotEvoImpact BRL ### WRONG ###] ) Sub DAXs: Based LY Realized Obj Bil _PilotEvoImpact BRL ### WRONG ### = CALCULATE ( SUM ( MiddleOffice[BILLING BRL] ); ALL ( 'Date'[Year] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) - CALCULATE ( SUM ( MiddleOffice[BILLING BRL] ); ALL ( 'Date'[Year] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) * [PilotImpact BRL] <<<<<< PROBLEM IS HERE 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] ) ) * [PilotImpact EUR] <<<<<< PROBLEM IS HERE
Correct Outcome:
Based LY Realized Obj Bil _PilotEvoImpact = IF ( HASONEVALUE ( 'Currency'[Currency] ); SWITCH ( VALUES ( 'Currency'[Currency] ); "BRL"; [Based LY Realized Obj Bil _PilotEvoImpact BRL]; "EUR"; [Based LY Realized Obj Bil _PilotEvoImpact EUR] ); [Based LY Realized Obj Bil _PilotEvoImpact BRL] ) Sub DAXs: Based LY Realized Obj Bil _PilotEvoImpact BRL = CALCULATE ( SUM ( MiddleOffice[BILLING BRL] ); ALL ( 'Date'[Year] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) - CALCULATE ( SUM ( MiddleOffice[BILLING BRL] ); ALL ( 'Date'[Year] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) * VALUES ( PilotEvolution_Table[% Evolution] ) <<<< I can't use this way (hardcoding using a disconnected slicer) Based LY Realized Obj Bil _PilotEvoImpact EUR = CALCULATE ( SUM ( MiddleOffice[BILLING EUR] ); ALL ( 'Date'[Year] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) - CALCULATE ( SUM ( MiddleOffice[BILLING EUR] ); ALL ( 'Date'[Year] ); SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) * VALUES ( PilotEvolution_Table[% Evolution] ) <<<< I can't use this way (hardcoding using a disconnected slicer)
Thanks a lot and I hope you have a better idea
Jimmy
Solved! Go to Solution.
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/
MyMeasure = [Based LY Realized Act Bil] - [Billing Pilot Evolution %] ?
Hi @Greg_Deckler, thanks for your suggestion, but didn't work. Please see attached pic.
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.
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/
No problem.
I forgot to say. Nice work on the full explaination of the problem and providing lots of detail including sample data. This makes it easier to provide help/answers.
Good luck.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |