Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JayKarim
Frequent Visitor

Measures, new columns for separate YOAs

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

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
V-pazhen-msft
Community Support
Community Support

@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
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks Pete, this is super helpful!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors