Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI ,
I need help with writing a measure where the results are different for row level and total level in a table visualization.
The target is to show the sum of sales for the first 3 years.
Opportunity ID | Year | Sales |
1 | 2019 | 10 |
1 | 2020 | 20 |
1 | 2021 | 30 |
1 | 2022 | 40 |
2 | 2018 | 50 |
2 | 2019 | 60 |
2 | 2020 | 70 |
2 | 2021 | 80 |
Needed Result :
Id | First 3 Year sum | |
1 | 60 | sum of 2019,2020,2021 |
2 | 180 | sum of 2018,2019,2020 |
Total | 240 |
But my measure does the calculation of this measure at total level and hence sums up 2018,2019 and 2020 since these are the first 3 years.
Meausre :
thanks
@85dhruv Please try this measure. In case you want to include more columns in your final table please add those columns in summarize function.
Measure =
VAR _sales = CALCULATE(SUM('Table'[Sales]),TOPN(3,'Table','Table'[Year],ASC))
VAR _table = SUMMARIZE('Table','Table'[Opportunity ID],"_totalsales",_sales)
RETURN IF(HASONEVALUE('Table'[Opportunity ID]),_sales,SUMX(_table,[_totalsales]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |