Search code examples
duplicatesdaxpowerpivot

How do I write a measure in this power pivot table that will only sum values next to a unique value?

I want to sum 'hours' in this table. Every 'item's' hours should be counted once, even if it appears twice. So Group A has 12.25 hours, in the example below.

Here is the source table:

enter image description here

A PowerPivot gives me:

enter image description here

So it's double counting rows where 'item' occurs twice, of course.

Because the 'hours' for different 'items' aren't the same, I'm not sure how to write a DAX measure to make this work in the pivotable (this is just an example, real dataset is the same problem but much larger). I tried

=([Sum of Hours]/COUNT([Hours]))*DISTINCTCOUNT([Item])

However it's not the correct calculation. It gave me 9.84375 for group A (right answer 12.25) and 47.53125 for group B (44 is correct).

You can see this from a deduped list (for unrelated reasons, it's not feasible to dedupe the list).

enter image description here

What measure (or combo of them) is going to give me what I need?

Thanks!

Solution 
  • CALCULATE( SUMX( VALUES( Table1[Item] ), CALCULATE( MIN( Table1[Hours] ) ) ) )

    Sorry for the delay. Your last request helped distract me after I messed up in an interview.

    I tried to make it as simple as possible:

    enter image description here

    The outer CALCULATE would be necessary only if you want to overwrite the filter contexts (slicers, row headers, column headers) present in your report.

    Which route to take to learn DAX depends on your available time. I have always been a believer that your first approach to this type of language should be something practical, more focused on scenarios and solutions so that you do not lose the will to learn (Learn to Write DAX A Practical Guide to Learning Power Pivot for Excel and Power BI by Matt Allington). Once you are interested in the topic, you can go to mid-level books like (Microsoft Excel 2013: Building Data Models with PowerPivot, Analyzing Data with Microsoft Power BI and Power Pivot for Excel ...) or jump directly to The Definitive Guide to DAX: Business intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel (Second Edition). Finally, understanding the language is about 80% of the way, you need practice and identify patterns: DAX Patterns (Russo, Marco Ferrari, Alberto) and all the SQLBI resources will be very helpful for you.