cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Reply
bajimmy1983 Member
Member

Dynamic calculation based in two different tables

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:

  • Measures A, B, C and D come from a table called Pilot (table A); 
  • Measure E comes from a table called MiddleOffice (table B); 
  • Wrong Outcome card is a measure where I am trying to get an outcome combining two other results (from table A and table B). 
  • Correct Outcome card is a representation of what I expect to see when I try to automate. Here I am hardcoding 0,0583. 

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 Smiley LOL

 

Jimmy

Jaderson Almeida
Business Coordinator
1 ACCEPTED SOLUTION

Accepted Solutions
stretcharm Senior Member
Senior Member

Re: 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/

 

 

 

 

 

10 REPLIES 10
Super User
Super User

Re: Dynamic calculation based in two different tables

MyMeasure = [Based LY Realized Act Bil] - [Billing Pilot Evolution %] ?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


bajimmy1983 Member
Member

Re: Dynamic calculation based in two different tables

Hi @Greg_Deckler, thanks for your suggestion, but didn't work. Please see attached pic. 

 

HelpPrint_2.PNGGreg's suggestion

Jaderson Almeida
Business Coordinator
stretcharm Senior Member
Senior Member

Re: Dynamic calculation based in two different tables

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)
bajimmy1983 Member
Member

Re: Dynamic calculation based in two different tables

Hi stretchable! Thanks for your reply. After dinner I will validate it and check the result. If ok I mark as the solution. I confess I forgot to round up.
Jaderson Almeida
Business Coordinator
bajimmy1983 Member
Member

Re: Dynamic calculation based in two different tables

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. 

 

HelpPrint_3.PNGFailed

Jaderson Almeida
Business Coordinator
stretcharm Senior Member
Senior Member

Re: Dynamic calculation based in two different tables

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.

 

Capture.PNG

 

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/

 

 

bajimmy1983 Member
Member

Re: Dynamic calculation based in two different tables

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 Smiley Sad

 

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. ... "

Jaderson Almeida
Business Coordinator
stretcharm Senior Member
Senior Member

Re: 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/

 

 

 

 

 

Highlighted
bajimmy1983 Member
Member

Re: Dynamic calculation based in two different tables

Thank you so much @stretcharm. I'll do the homework for sure. Your help was fantastic. ๐Ÿ‘๐Ÿ‘

Best regards,
Jimmy
Jaderson Almeida
Business Coordinator