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
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.
but for the target i dont need to do sum , i just need to show it.
and how can i do target-sales?
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.