Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power BI community,
I have a data table with the following 3 fields:
1. How do i create a measure to seprate YOA data for 2019, 2020 and 2021 into individual measures?
Many thanks in advance
Solved! Go to 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
Proud to be a Datanaut!
@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])))
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
Proud to be a Datanaut!
Thanks Pete, this is super helpful!