Sum of max values filtered by year. Help

[Anonymous]

Hello everyone. 

I have a table like this

Date	Country	Value
25/12/2017	Poland	10
26/12/2017	Brazil	5
27/12/2017	Germany	15
28/12/2017	Poland	11
29/12/2017	Poland	10
30/12/2017	Poland	12
31/12/2017	Canada	14
01/01/2018	Germany	16
02/01/2018	Poland	13
03/01/2018	Germany	15
04/01/2018	Brazil	6
05/01/2018	Canada	15
06/01/2018	Poland	14
07/01/2018	Brazil	7

I'm trying to make a report that shows the sum of the max values of each country filtered by year. So for 2017 it would be 46 and for 2018 it would be 52.

I'm really struggling with this, would appreciate some help.

Thanks in advance

[Ashish_Mathur]

Hi,

You may refer to my solution in this file.

Hope this helps.

[OwenAuger · Accepted solution]

Hi @Anonymous

First of all, in order to easily filter by Year, add a Calendar table with a Year column related to your data table, or a Year column in your data table.

Then create a measure like this (replace Data with your actual table name):

Sum of Country Max Values =
SUMX ( VALUES ( Data[Country] ), CALCULATE ( MAX ( Data[Value] ) ) )

[Anonymous]

Hello @OwenAuger. Thank you so much for the response, it really did it! I have a question though. Why when I did the relation between the date table and my data table, the date from my data table can no longer use the "Date Hierarchy"?

I am filtering the date using "Between Dates" but I was wondering why I couldn't use the Date Hierarchy anymore

Thanks again for the help

[OwenAuger]

That's good news 🙂

When you relate a date column from you data table to a date table, you should apply any filters on the date table rather than the data table.

It looks like the hierarchy on the data table date column which previously existed (which is really a behind-the-scenes date table) is automatically disabled when you create a relationship like this.

However, a date hierarchy is available on the date table, as long as you don't use the Mark as Date Table function.

[Anonymous]

I see what's happening. Thanks again @OwenAuger I really appreciate it.