Create a measure that returns the sum of all orders for country and year in context

Question

I have a table of orders, with contains seller id, order date, amount and country (among other fields). I need a measure that calculates the sum of amounts for the country and year in context. So, for example:

• If I create a table visual with fields 'year' and this measure, it will show the total amount for all countries in each year
• If I create a table visual with fields 'year', 'country' and this measure, it will show the total amount for each country in each year
• If I create a table visual with fields 'date', 'country' and this measure, it will not show the total amount for this day and country, but keeps showing the year total for the country
• If I create a table visual with fields 'seller id', 'date', 'country' and this measure, it will override the 'seller id', and keeps showing the year total for the country

This measure, of course, will be affected by any 'year' or 'country' filter active in the dashboard

I will use this measure later for many purposes. For example, filter in the dashboard only the countries that, for the year selected in context, have ordered more than certain amount

Can you help me, please?

Answer 1

Have a look at the ISINSCOPE function.

Your measure could look like:

Your Measure = 
  var thisYear = YEAR(MAX(YourTable[Date]))

  return SWITCH(TRUE(),
    ISINSCOPE(YourTable[Seller Id]) && ISINSCOPE(YourTable[Date]),
      CALCULATE( SUM(YourTable[Amount]), REMOVEFILTERS(YourTable[Seller Id]), REMOVEFILTERS(YourTable[Date]), YourTable[Year] = thisYear),

    ISINSCOPE(YourTable[Seller Id]),
      CALCULATE( SUM(YourTable[Amount]), REMOVEFILTERS(YourTable[Seller Id])),

    ISINSCOPE(YourTable[Date]),
      CALCULATE( SUM(YourTable[Amount]), REMOVEFILTERS(YourTable[Date]), YourTable[Year] = thisYear),

    // else
      SUM(YourTable[Amount])
  )