Forum Discussion

Vemul's avatar
Vemul
Helper I
1 year ago
Solved

Matrix data presentation for different groups

Hello Guru's 

 

I am new to power Bi and truing to work on Matric display. 

 

I have data for differnt groups with differnt types, I need to present data in Matrix table on individual section.  instead of creating multiple measures for each group is there any smart way that we can write single query to show details for individually.


Thanks for your support 

example 

data table 

GroupTypeCount
816CA5
816CA3
816CA4
995BA2
995BA7
995BA8
816CC3
995BC6
816CD1
816CD0
995BD5
995BD2
995BD7
995BS3
995BS5

 

Expected result 

GroupTypeCount
816CD1
816CA12
816CC3
816CTotal16
995BD7
995BA17
995BC6
995BS8
995BTotal38
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi Vemul 

    You can use a matrix visual with an impicit measure

    or an explicit one that sums the count column

    Sum of Count =
SUM ( 'Table'[Count] )

    This aside, your expected result doesnt match  your sample data. 995B should be 45 and not 38

7 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    1 year ago

    Vemul 

    I'd suggest you keep using Matrix table even though it does not render exactly the same format as in your second picture. 

    I'd also recommend using Outline for Layout, disabling '+/- icon' under Row Header and only showing Subtotal for Row level 'Type'. 

     

     

     

     

  • Ray_Minds's avatar
    Ray_Minds
    Solution Supplier
    1 year ago

    Hi  Vemul 

    Create a Measure on the Count Column

    Go to the Modeling tab in Power BI and create a new measure using the formula below:

    Total Count = SUM(Table[Count])

    This measure will calculate the total count for each combination of Group and Type.

    • Select the Matrix visual and add it to your report canvas.
    • Drag and drop the Group column, then the Type column under the Rows section and, Drag and drop the measure Total Count into the Values section.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Vemul 

    You can use a matrix visual with an impicit measure

    or an explicit one that sums the count column

    Sum of Count =
SUM ( 'Table'[Count] )

    This aside, your expected result doesnt match  your sample data. 995B should be 45 and not 38

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi Vemul ,

    You can achieve this in Power BI without creating multiple measures by leveraging the built-in Matrix visual and DAX’s “Show Values as” options.

    Here’s how you can do it:

    1. Load your data into Power BI as a simple table (as in your example).

    2. Create a Matrix visual:

      • Drag the Group field into the Rows area.

      • Drag the Type field below Group in the Rows area (to create a hierarchy).

      • Drag the Count field into the Values area and set the aggregation to Sum (default).

    3. Enable subtotals to get the “Total” row for each group:

      • Click on the Matrix visual, go to the Format pane, and expand the “Subtotals” section.

      • Turn on “Row subtotals.” This will automatically show totals for each group, just as you expect.

    4. No need to create extra measures—Power BI Matrix will sum up the counts by Group and Type, and add totals for you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi Vemul,

    I hope you had a chance to review the solution shared by danextian MasonMA rohit1991 . If it addressed your question, Consider accepting the helpful response as solution it helps others find answers more quickly.
    If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

     

    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi Vemul,

    checking in to see if your issue has been resolved. If the response provided was helpful, kindly mark the helpful reply as the solution so that others with the same issue can benefit.
    let us know if you still need assistance.

     

    Thank You.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi Vemul,

    We haven’t heard back from you in a while. If your issue has been resolved, Mark the relevant response as the solution to help fellow community members.
    If you still need support, let us know if you still need assistance.

     

    Thank You.