Forum Discussion
how to create sum by dates column
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
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.
sorry,didnt understand it.. i'm new on the dax language
can you give me an example? or the syntax?
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!
but if i whant it like a visual charts?
this solution is work only in this visual right?
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.