How to SUM DISTINCT Values in a column based on a unique date in another column of a Power BI table

Question

I have a table in Power BI, where I have two columns like Date and Daily Targets. Daily Targets are always same on the same date so I need a measure to only SUM 1 value for 1 date instead of calculating every row because these two columns contains duplicate values. Please see at attached screenshot for the data table.

As you look into my data, there are two distinct dates and all I need is when I add this Daily Target Column in any visualization, instead of adding 11653+11653+11653 for 3rd Jan, it should only Sum 11653 for 3rd Jan. Please help me with it, I will be very grateful to you.

Solution

To get a measure that takes the maximum value of the Daily Target by date, you can do something like this:

Daily Target = SUMX(GROUPBY(Table1, Table1[Date], "Max Daily Target", MAXX(CURRENTGROUP(), [DailyTarget])), [Max Daily Target]) 

Assuming your table is called Table1

The inner GROUP BY says to identify the highest daily target for each date. This assumes any given date will only have a single daily target (you could equally pick the MIN or AVG as they should all result in the same number). Note, if you have a single date with 2 different daily targets, this formula will fall down because it will only pick the biggest.

The outer SUMX sums each day's biggest daily target. This is important if you are aggregating by month or year. At the end of January, you want to have up to 31 daily targets added together.

---

Note: In general, I would roll up the daily target by day before loading the data into Power BI. It's not fully clear from your screenshot why you have records at a lower granularity, so I can't explain how I'd do it in your particular case. However, this post by DAXPatterns.com does go into how to handle "sales vs. budget", which may be relevant to you: http://www.daxpatterns.com/handling-different-granularities/