Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBI_learn_NK
Frequent Visitor

Need Help Displaying Non-Summarized Amounts and Subtotals in Matrix Visual

Hello experts , 

I'm working on a report that includes the following fields:

  • Report_Period
  • Invoice_Date
  • Currency
  • Type
  • Client_name
  • Manager_name
  • Amount

    My goal is to display each transaction's amount individually without summarization while also including subtotals in a Matrix visual.  

    Here's what I've tried so far:

    1. Added Report_Period, Invoice_Date, Currency, Type, Client_name, Manager_name, and Amount to the Rows section of the Matrix visual.
    2. Added the Amount field to the Values section, which resulted in summarization that I don't want.

      Workaround Attempted:

      • Added Amount to both Rows and Values sections. This displays the individual amounts correctly and also includes subtotals.
      • However, this results in the Amount field being displayed twice – once in the rows and once in the values.

        Issue:

        • I need to hide the Amount column in the Rows section while keeping the individual transaction amounts visible and correctly subtotaled.

          My Requirements:

          • Show individual transaction amounts without summarization.
          • Include subtotals at appropriate group levels.
          • Avoid duplicated Amount fields in the Matrix visual.
          • Hide the Amount column in the Rows section while keeping the correct display in the Values section.

            Any suggestions or best practices to achieve this? I've tried turning off the Stepped layout and adjusting subtotal settings, but I'm still facing issues with hiding the redundant Amount column in the Rows section.

            Thank you for your help!

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

Hi @PBI_learn_NK ,

 

If you'd like to show the transaction amounts without aggregation in a matrix visual, one way to do this is to add an index column in Power Query before bring your fact table in Power BI data model, and then putting the index column in the matrix to prevent aggregation of numerical values.  I think there is also a way to hide that index column if you'd like to hide it.  The resultant output is like below.  If this is not what is being requested, a further help on subtotal visualization can be obtained in this forum.  

DataNinja777_1-1720321391975.png

 

I attach an example pbix file.  

Best regards,

 

View solution in original post

3 REPLIES 3
DataNinja777
Super User
Super User

Hi @PBI_learn_NK ,

 

If you'd like to show the transaction amounts without aggregation in a matrix visual, one way to do this is to add an index column in Power Query before bring your fact table in Power BI data model, and then putting the index column in the matrix to prevent aggregation of numerical values.  I think there is also a way to hide that index column if you'd like to hide it.  The resultant output is like below.  If this is not what is being requested, a further help on subtotal visualization can be obtained in this forum.  

DataNinja777_1-1720321391975.png

 

I attach an example pbix file.  

Best regards,

 

@DataNinja777 

Thank you for the suggestion!


This solution works perfectly for showing transaction amounts without summarization. However, since adding an Index column in Power Query is not supported with DirectQuery, I am switching to the Import mode to implement this change.

Thanks again for your help!

foodd
Super User
Super User

Hello @PBI_learn_NK , remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

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

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.