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.
I need urgent help on a SUM formula please!
I am trying to make a matrix table which sums up a number for the entire year, even when the table shows quarterly periods.
For example, there is $5k of sales to renew across the whole of 2022, with differing amounts per quarter. I would like the output of my formula to show $5k for each of the quarters and not their individual portfolio . I have added the formula below which I thought should work.
I have added a summary of the data below, thanks in advance!
Solved! Go to Solution.
hi @robg89
try to plot the a like:
Amount =
CALCULATE(
SUM(Table1[Portfolio]),
FILTER(
ALL(Table1[Qtr]),
YEAR(Table1[Qtr])=YEAR(MAX(Table1[Qtr]))
)
)
hi @robg89
try to plot the a like:
Amount =
CALCULATE(
SUM(Table1[Portfolio]),
FILTER(
ALL(Table1[Qtr]),
YEAR(Table1[Qtr])=YEAR(MAX(Table1[Qtr]))
)
)
After looking at the data again, I still need help. If I look at it by month, the months where there are no portfolios are blank, I need these to also show $5k. Can you please help?
The data hasn't changed. I am trying to look at the portfolio values monthly therefore in the above example, I would like to see $5k in every month.
To put in to practical terms, at the start of the year (opening portfolio) I have $5k,this renews across 4 quarters. Every month we have sales adding to the portfolio but I want the opening portfolio to stay the same,
hi @robg89
then you need another table, with all the MonthEndDate and connected with your datatable.
Feed your visual with MonthEndDate column.
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |