Measure to return the total value of the group (country and year)

[AnthonyJoseph]

Hello experts,

I have a table visual (my input data table as in screenshot below), which has details per country/year/entity. The request is to create a measure that can sum the difference based on the combination of country and year only (ignoring the entity) and to have that as a column in table visual. 

For example: For client A, Country "United States" has records for three entities for year 2022 so the output measure should be 521+100+216 = 837.
Similarly, For client B, Country "Australia" has records for two entities for year 2021 so the output measure should be 228+500 = 728.
Please can some one provide some guidance. Below are the Input and expected output in screenshot.

Any help is highly appreciated!

[JamesFR06]

Hi

Please try this code : 

Mesure01 = calculate(sum(TableTotal[Amount]),ALLEXCEPT(TableTotal,TableTotal[Client],TableTotal[Country],TableTotal[Year]))

[AnthonyJoseph]

Hi @JamesFR06  I m getting a different answer, please can you help me. I m getting the same value for all countries and years, please see screenshot below. 

[JamesFR06]

Hi @AnthonyJoseph 

There some relation layong in your model.

With this code it will be ok

calculate(sum(TableTotal[Amount]),TableTotal[Client]=Clt&&TableTotal[Country]=Ctry&&TableTotal[Year]=Annee,all(TableTotal))

[AnthonyJoseph]

@JamesFR06  Looks like the measure is incomplete (TableTotal[Client]=Clt&&TableTotal[Country]=Ctry&&TableTotal[Year]=Annee ). Would be really nice if you can share the complete measure.

[Greg_Deckler]

@AnthonyJoseph This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[AnthonyJoseph]

Thanks @Greg_Deckler 
The solution is giving me the same value across the table. Please can you help me understand, where I m going wrong.