Matrix

[RUION]

Hi, 

I want to create the below table in PowerBi, and I am using a Matrix for it: 

I have a table that holds all the Titles and the values that should be reported every month. I also have a column that holds the date from which the value should be calculated.  I have a status column based on which only certain Titles should be displayed.

I created a separate table that holds all months of the year, and a column called Status that connects the two tables. 

If I drag the the monthly value into the matrix, then the value is displayed every month. The value should be displayed starting with the first reporting month and continue for a 12 months (as in the examples posted above). 

Does anyone have any suggestions on how to do this in PowerBi?

[v-shex-msft]

Hi @RUION,

I think you need to create a measure to summarize records who has the correct states and ignore calculate on others. Can you please share some sample data for test? 

Regards,

Xiaoxin Sheng

[RUION]

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

[v-shex-msft]

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

[RUION]

Hi Xiaoxin, 

This solution solves most of my problem, but how can I have the total column correctly calculate the amount?  

[v-shex-msft]

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

[RUION]

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?