Exclude Matrix Column from Subtotal

[Anonymous]

Hi All,

I am using a Matrix visual to display my data. My data is broken out into five columns provided by the customer:

- Year

- Month

- Tier

- Number of Records

- Percent of Records

The Percent of Records adds up to 100% for each month. However, the 100% is being summed for each month, showing a much larger number in the Subtotals row. My matrix currently looks like this:

Year                                      Number                                     Percent

2020                                     3,000                                          200%

   September                        1,000                                          100%

      High                                 200                                            20%

      Low                                  300                                            30%

      Medium                           500                                            50%

   October                            2,000                                          100%

      High                                 300                                            15%

      Low                                  600                                            30%

      Medium                         1,100                                            55%

   Total                                 3,000                                           200%

As you can see, each month has their tiers, as well as numbers and percentages associated with them. Everything looks good except for the subtotal row. This correctly totals the 1,000 and 2,000 from the two months to get 3,000 total records. However, it is summing the 100% from each month as well to get 200%, which is not relevant. Is there any way to simply remove/exclude the Percent column from the subtotal display?

Thanks!

Matt

[Icey]

Hi @Anonymous ,

How is your measure written?

I don't know your data structure and create a simple example. Try to create a measure like so:

Percent of Records =
SUM ( 'Table'[Number of Records] )
    / CALCULATE (
        SUM ( 'Table'[Number of Records] ),
        ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Year] )
    )

If it doesn't work, please share us some sample data for test.

Best Regards,

Icey

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

[edhans]

It is likely going to be some combination of using HASONEFILTER(), ISINSCOPE(), HASONEVALUE, or other similar functions that will resolve this. But we need data to work with in a decent format. I cannot copy your matrix sample into usable data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum