Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |