Forum Discussion

MGierach's avatar
MGierach
Frequent Visitor
2 years ago

Matrix Visual - Totals Visible Only For Aggregration

Hey folks,

 

Is there an easy way, that I'm obviously not aware of, or a sophisticated way, ie. dax, of having totals show in the matrix visual only when there is aggregration occurring?

 

For example if I have period fields in my columns and have only a single month or year selected do not show the totals because it is just a duplication of the data in the matrix but if I have multiple months or years selected show the totals because I want to see the aggregation accross those periods.

 

Thanks

Need Help

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    If you want the column/row to remain but just the figure to disappear, then use the ISINSCOPE() function along with the IF() function.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi MGierach 

     

    Just like Ashish_Mathur said, you can use isinscope() and if() or switch() to get the target.

    Please try this:

    First of all, I create a set of sample:

    Then add a measure:

    SumValue = 
IF (
    ISINSCOPE ( 'Table'[Month] ),
    SUM ( 'Table'[Value] ),
    IF (
        HASONEVALUE ( 'Table'[Month] ),
        BLANK (),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table' ) )
    )
)

    The result is as follow:

    If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

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

    • MGierach's avatar
      MGierach
      Frequent Visitor
      2 years ago

      Thanks, I'm looking to see if it is possible to only have the total columns show up when there are actually totals to calculate. I've thought about doing as you suggested but that still leaves the columns in the matrix with blank cells. I'd like to have a cleaner looking visual for single selected periods. As shown below where totals are useful when looking at Year 1 and Year 2 but totals are not useful when looking at Year 2 in isolation.