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

     

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.

       

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

      Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

      Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

      Please show the expected outcome based on the sample data you provided.

      Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

      • Bea8's avatar
        Bea8
        Regular Visitor
        1 year ago

        thank you!! very helpful!