Forum Discussion

davidi4524's avatar
davidi4524
Helper III
9 years ago
Solved

how to create sum by dates column

hi ' i have two tables:

1. sales per date columns: branch,date,sale

2.target per date columns: branch,date,target

 

i need to calculate the the target - total sales per date

my problem is that the total sales per date is built for the sum of all the sales lines per date, for example:

 

date             sale

18.6.17         10 $

18.6.17          15$

18.6.17           17$

now, i have a relationship between target tables and sales table by concatenate the date column and the branch column

 

hoe do i bring to the target table a column that will show me the sum of sales per date from the sales tables?

and how to i create another column that will show me the target - total sale per date?

 

for eg.

date       target     total sales 

18.6.17     100 $       42$

 

thank you very much!

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    davidi4524,

     

    You can use the DAX expression below to get your result.
    Sales table:
    Column = LOOKUPVALUE(Target[Target],Target[Date],Sales[Date])

    Target table

    Column = CALCULATE(SUM(Sales[Sales]),FILTER(ALL(Sales),Sales[Date]=Target[Date]))

     

    Regards,

    Charlie Liao

9 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    davidi4524,

     

    You can use the DAX expression below to get your result.
    Sales table:
    Column = LOOKUPVALUE(Target[Target],Target[Date],Sales[Date])

    Target table

    Column = CALCULATE(SUM(Sales[Sales]),FILTER(ALL(Sales),Sales[Date]=Target[Date]))

     

    Regards,

    Charlie Liao

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      is it an updated answer to this question because LOOKUPVALUE isnt a function in power query now

       
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    You could make use of a matrix that uses the Date and/or branch as its Rows. Then for the Values you can put in the Sales and Target columns, selecting to Sum them.

    • davidi4524's avatar
      davidi4524
      Helper III
      9 years ago

      sorry,didnt understand it..  i'm new on the dax language

      can you give me an example? or the syntax?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        When on the "Report" section of Power BI, where you make the graphs and charts.  In the Visualisation Pane, there is a set of Visuals to choose from.  In there is a visualisation called "Matrix".  If you take your Date and/or Branch columns and drag them into the Rows box, you will get a matrix that will show the Dates/Branches grouped.

         

        Next if you take your Sales and Target columns and drag them into the Values box, you will get summarized information, per Date/Branch of the values in the Sales and Target columns.  You will need to make sure those columns are marked as numbers, which is as easy as clicking on those columns and checking what is shown in the Modelling tab of your options ribbon.

         

        If you have your Sales and Target columns as a number, when you click on the down arrow in the Values box of your visualisation options, you can select how you wish to display the values in the Matrix.  You will have a choice of options like Sum, Average, etc.

         

        You won't need to write any measures at all!