Forum Discussion

miemer's avatar
miemer
Regular Visitor
5 years ago
Solved

InOut report

Hi everyone, I'm new to power bi even though I've built several reports for work. Now I have this problem: I would like to build a table in which the first column is the date while the second column is populated by taking values ​​from two tables (sql and Filemaker via odbc). The data from sql are negative, the consumption of a raw material while the data from Filemaker are the income of that matter (positive values). I would like to represent the entire progression of consumption by showing the trend to understand if I will have problems before the expected date of receipt of the goods...

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  miemer ,

    Sumx returns the sum of the expressions evaluated for each row in the table.

    You can also use these methods to achieve the desired effect:

    According to your description, I create this data:

    Table Filemaker:

    Table SQL:

    Here are the steps you can follow：

    Associate the three tables with the date column:

     

    The first method:

    Create calculated column.

    trend_way1 =
var _sql=CALCULATE(SUM('SQL'[Consumption of raw materials]),FILTER('SQL','SQL'[date]=EARLIER('Table'[date])))
var _File=CALCULATE(SUM('Filemaker'[Material income]),FILTER('Filemaker','Filemaker'[date]=EARLIER('Table'[date])))
return
_sql+_File

    The second method:

    Create calculated column.

    trend_way2 = RELATED(Filemaker[Material income])+RELATED('SQL'[Consumption of raw materials])

    Result：

    Finally put it into the line chart for display

    You can downloaded PBIX file from here.

     

    Here is the link of the Dax function, I hope it will help you:

    https://docs.microsoft.com/en-us/dax/related-function-dax

    https://docs.microsoft.com/en-us/dax/sumx-function-dax

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    I would say you'll need three tables in your PBI data model: DimDate, SQL and Filemaker

    Relate the SQL and Filemaker to DimDate using Date or DateKey columns. 

    https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

     

    Then you can create a table visualization with DimDate[Date] as the first column. Create a measure for the second column. New MEASURE:

    StockOnHand= SUMX(DimDate, CALCULATE(SUM(SQL[Consumption])) + CALCULATE(SUM(Filemaker[Income])) )

     

    Then use this in a line chart and use the forecast analytics to see trends. 

    • miemer's avatar
      miemer
      Regular Visitor
      5 years ago

      Thanks for the valuable reply, I will try the proposed solution as soon as possible. To take advantage: The SUMX function will retrieve the values ​​from the two tables (sql and filemaker) according to the date in the first column, correct?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  miemer ,

    Sumx returns the sum of the expressions evaluated for each row in the table.

    You can also use these methods to achieve the desired effect:

    According to your description, I create this data:

    Table Filemaker:

    Table SQL:

    Here are the steps you can follow：

    Associate the three tables with the date column:

     

    The first method:

    Create calculated column.

    trend_way1 =
var _sql=CALCULATE(SUM('SQL'[Consumption of raw materials]),FILTER('SQL','SQL'[date]=EARLIER('Table'[date])))
var _File=CALCULATE(SUM('Filemaker'[Material income]),FILTER('Filemaker','Filemaker'[date]=EARLIER('Table'[date])))
return
_sql+_File

    The second method:

    Create calculated column.

    trend_way2 = RELATED(Filemaker[Material income])+RELATED('SQL'[Consumption of raw materials])

    Result：

    Finally put it into the line chart for display

    You can downloaded PBIX file from here.

     

    Here is the link of the Dax function, I hope it will help you:

    https://docs.microsoft.com/en-us/dax/related-function-dax

    https://docs.microsoft.com/en-us/dax/sumx-function-dax

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.