Forum Discussion

Bea8's avatar
Bea8
Regular Visitor
1 year ago
Solved

Power BI Built Matrix table

Hi,

I'm new to Power BI and I'm seeking guidance on the best approach for the following situation.

Situation:
The data that I have looks like this:

 

 

And I need to create a report that looks like this:

 

 

I was thinking of using the matrix feature for the report.
But not sure if i need to transform or maybe create new tables under the query to be able to create the report previously mentioned.
Because these are some of my concerns:
How to get the count of "Done?" for each Name_2.
How do get the date of the week (for example it has to be the beginning of the week)

 

Sorry for all the questions, but any help or guidance will be appreciated!

thank you! 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from lbendlin , please allow me to provide another insight:

    Hi, Bea8 

    Regarding the issue you raised, my solution is as follows:

    1.First I have created the following table and the column names and data are the data you have given:

    2.Create calculated column references:

    SortColumn = 
VAR c2 =
    RANKX (
        'Table',
        WEEKNUM ( 'Table'[Date Done] ),
        WEEKNUM ( 'Table'[Date Done] ),
        DESC,
        DENSE
    )
RETURN
    IF ( ISBLANK ( 'Table'[Date Done] ), BLANK (), "week" & c2 )

    3.Below are the measure I've created for your needs:

    Measure = CALCULATE(COUNT('Table'[Done?]),'Table'[Done?]="Yes")

    4.Then, apply the column to the matrix view:

    4.Next, modify the layout in the following position:

    5.Here's my final result, which I hope meets your requirements.

    Please find the attached pbix relevant to the case.

     
     

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    The general approach is to unpivot your data to bring it into a usable format.  Then the matrix visual will "just work".

     

    Where is the week information in your sample data?

    • Bea8's avatar
      Bea8
      Regular Visitor
      1 year ago

      sorry I didn't include that, i don't have "week" but I do have "Date Done" 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for the reply from lbendlin , please allow me to provide another insight:

        Hi, Bea8 

        Regarding the issue you raised, my solution is as follows:

        1.First I have created the following table and the column names and data are the data you have given:

        2.Create calculated column references:

        SortColumn = 
VAR c2 =
    RANKX (
        'Table',
        WEEKNUM ( 'Table'[Date Done] ),
        WEEKNUM ( 'Table'[Date Done] ),
        DESC,
        DENSE
    )
RETURN
    IF ( ISBLANK ( 'Table'[Date Done] ), BLANK (), "week" & c2 )

        3.Below are the measure I've created for your needs:

        Measure = CALCULATE(COUNT('Table'[Done?]),'Table'[Done?]="Yes")

        4.Then, apply the column to the matrix view:

        4.Next, modify the layout in the following position:

        5.Here's my final result, which I hope meets your requirements.

        Please find the attached pbix relevant to the case.

         
         

        Best Regards,

        Leroy Lu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.