Forum Discussion
Matrix
Hi,
here is the Sample data.
I need help to:
1. have all months of the year displayed in the table.
2. for each Title, to have the Benefitspermonth displayed for each month, starting with the Deliver benefits fro month.
3. the total on each row should show the amount of benefits in the current year
HI RUION,
You can refer to below steps to achieve your requirement.
Steps:
1. Use calendar month as column of matrix.
2. Write a measure to calculate total amount based on title and calendar month.
Fill All Month =
CALCULATE (
SUM ( 'The Bucket'[BenefitsPerMonth] ) + 0,
ALLSELECTED ( 'The Bucket' ),
VALUES ( 'The Bucket'[Title] )
)
Result:
BTW, I think this graph not so suitable when one title contains multiple values. In my opinion, I'd like to fill and summary the amount which after current date.
Fill after =
CALCULATE (
SUM ( 'The Bucket'[BenefitsPerMonth] ) + 0,
FILTER (
ALLSELECTED ( 'The Bucket' ),
MONTH ( 'The Bucket'[Deliver Benefits fro] ) <= MONTH ( MAX ( 'Calendar_Dates'[Date] ) )
),
VALUES ( 'The Bucket'[Title] )
)
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
This solution solves most of my problem, but how can I have the total column correctly calculate the amount?
HI RUION,
After test, I found this issue caused with your calendar table. It lost two month records so condition can't find out correspond records based on current calendar date.(max calendar date month is less than exited bucket table date)
When I test with full calendar with thirty-six records, it works well on total level.
Regards,Xiaoxin Sheng
Hi Xiaoxin,
The totals for columns are now correct (after correcting the calendar table, as you suggested). But the totals on rows are still incorrect and I can see the same issue in the screenshot you sent. Any ideas on how to fix that?