Measures, new columns for separate YOAs

[JayKarim]

Hi Power BI community,

I have a data table with the following 3 fields:

1. Product
2. Total Sales £
3. Year Paid (2019,2020,2021)

1. How do i create a measure to seprate YOA data for 2019, 2020 and 2021 into individual measures? 

Many thanks in advance

[Anonymous]

@JayKarim 
Do you want one measure or 3 measures for each year? For example, If you want to calculate sum([total sales]) grouped by [Product] and [Year]. You can create the following measure.

Measure = CALCULATE(SUM([Total Sales]), Filter(allselected(table),[Product]=MAX([Product])&&[Year]=MAX([Year])))

[BA_Pete · Accepted solution]

Hi @JayKarim ,

The way you would normally display this in Power BI is by creating a generic measure:

_sales = SUM(yourTable[Total Sales £])

You would then differentiate between years/products etc. by adding these columns into your visual that displays your measure. Power BI will handle the context filtering to correctly sum your sales in each of the different year/product groups. If you only want to show one year in each visual, then apply a visual or page-level filter that specifies the year required. This keeps coding and maintenance to a minimum.

If you REALLY want a separate measure for each year, you would create measures something like this:

_sales2019 =
CALCULATE(
  SUM(yourTable[Total Sales £]),
  yourTable[Year Paid] = "2019"
)

Pete

[JayKarim]

Thanks Pete, this is super helpful!