how to create sum by dates column

[davidi4524]

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!

[v-caliao-msft · Accepted solution]

@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]

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

[Anonymous]

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]

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

can you give me an example? or the syntax?

[Anonymous]

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!

[davidi4524]

but if i whant it like a visual charts?

this solution is work only in this visual right?

[Anonymous]

Similar logic applies to charts.  You can simply drag those fields into similar boxes.  For a chart, you would put the Date into the Axis box and could put the branch into the Legend box.  For values you would need to put in either the Sales or Target.  One line will represent each branch.

Conversely, if you leave the Legend box blank, you can put both Sales and Target into the values box and get a graph where 1 line represents each.

Now you might be thinking "Why wouldn't i put both Sales and Target in the first example?".  The answer is, by putting the branch into the legend, you are asking for 1 line per branch.  Its easy enough to simply have 2 charts on 1 page.

EDIT:  Also if you make use of Slicers, you can allow users to select Date ranges or branches etc to redraw the graphs.

[davidi4524]

but for the target i dont need to do sum , i just need to show it.

and how can i do target-sales?

[Anonymous]

Even if you have a single entry of a single value, you still need to use a formula that will handle aggregate data.  Power BI, ahead of time, doesn't know you will only ever have 1 value.  By telling it to sum, or average or whatever, you are instructing it on how to handle 1 or more rows of data.