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.

Leonard · Accepted Answer · 2017-03-22 21:22:26Z

5

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/

answered Mar 22, 2017 at 21:22

Leonard

2,5962 gold badges17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Farrukh Cheema Over a year ago

Thank you so much. it worked. I have one more thing to ask. I have another Date table, and another table where I have a billing data by date. I need to sum the value as zero in your provided DAX formula if the BillingAmount(Billing Table) on the same date is blank. Can you please help me with it?. Thank you once again

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related