Forum Discussion

mat007mat's avatar
mat007mat
Regular Visitor
6 years ago
Solved

formulas between different tables

dears,   I would like to get help on following issue - I am combining two tables, one with actuals sales and one with forecasted sales. I would like to build the following KPI: I want to sum up all...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello mat007mat 

     

    as you are writing about KPI I assume you need a solution in Power Pivot/DAX. I would prefer combining such tables in Power query and do the final calculation in DAX.

    However.. to solve this with DAX I saw no other solution to create a new table out of the forecast-table only using country, type and month. Then adding 3 new columns to calculate forecast, sales and error. the measure uses then sales and error to get the KPI.

    Find enclosed the file with my solution

    link 


    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

mat007mat's avatar
mat007mat
Regular Visitor
6 years ago

Dear Jimmy,

 

maybe one more question; you worked with the creation of a new table in which you combined data from two other tables.

In power bi I can add this additional table, but can this be done in power pivot as well?  (the creation of a new table only containing formulas?)

 

thanks!

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago
Hello

Don't understand fully your question.
Power pivot can be fed by power query. So you can query a data source, transforming it and hand over to power pivot, if this is what you meant.

All the best

Jimmy