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 ...
Need Help
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

      • RUION's avatar
        RUION
        Frequent Visitor
        7 years ago

        Hi Xiaoxin, 

         

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