Forum Discussion

RUION's avatar
RUION
Frequent Visitor
7 years ago

Matrix

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?

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      RUION
      Frequent Visitor
      7 years ago

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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