In matrix, omit blank rows, but assign zeros to blanks when some values in row are non-blank

[MadsEmil]

The data:

I have a dataset, that I would like to visualise in a matrix using a three-level row hierarchy and a date hierarchy in the columns. Some combinations of Group/Subgroup/Type and Year have missing values (see image below).

Desired output:

I would like to have zeros instead of blanks in the full matrix, as shown in the image below.

However, if a row has only blank values, I want it omitted from the matrix, as shown in the image below.

Summary:

How do I replace blank values with zeros when there are non-blank values in the row in the current context, but omit the row when there are only blank values in the current context. I have attached the sample data I've used in the above examples in the table below.

Thank you for your help 🙂

Group	Subgroup	Type	Year	Amount
Group1	Subgroup1	Type1	2020	200
Group1	Subgroup1	Type2	2020	110
Group1	Subgroup2	Type3	2020	100
Group1	Subgroup2	Type4	2020	50
Group2	Subgroup1	Type1	2020	40
Group2	Subgroup1	Type2	2020	20
Group2	Subgroup2	Type3	2020	19
Group2	Subgroup2	Type4	2020	10
Group3	Subgroup1	Type2	2020	-2
Group3	Subgroup2	Type4	2020	3
Group4	Subgroup1	Type1	2020	-192
Group4	Subgroup1	Type2	2020	-104
Group4	Subgroup2	Type3	2020	-95
Group4	Subgroup2	Type4	2020	-48
Group5	Subgroup1	Type1	2020	-32
Group5	Subgroup1	Type2	2020	-14
Group5	Subgroup2	Type3	2020	-15
Group5	Subgroup2	Type4	2020	-10
Group1	Subgroup1	Type1	2021	209
Group1	Subgroup1	Type2	2021	92
Group1	Subgroup2	Type3	2021	87
Group1	Subgroup2	Type4	2021	60
Group2	Subgroup1	Type2	2021	22
Group2	Subgroup2	Type3	2021	21
Group2	Subgroup2	Type4	2021	12
Group3	Subgroup1	Type1	2021	1
Group3	Subgroup1	Type2	2021	-2
Group3	Subgroup2	Type3	2021	-1
Group3	Subgroup2	Type4	2021	4
Group4	Subgroup1	Type1	2021	-167
Group4	Subgroup1	Type2	2021	-91
Group4	Subgroup2	Type3	2021	-86
Group4	Subgroup2	Type4	2021	-57
Group5	Subgroup1	Type2	2021	-16
Group5	Subgroup2	Type3	2021	-16
Group5	Subgroup2	Type4	2021	-12

[_AAndrade]

For the firts matrix with all year use my second measure it the IF statement.
And for the matrix of each year use your current measure, becaus PBI by default hide the blank values.

[MadsEmil]

Oh, I apologize, I have not been completely clear in formulating my question then. There should only be one matrix, which the user should be able to filter, among other things filter on Year. So the measure needs to be able to handle both behaviors.

[_AAndrade]

Hum ok. So I didn't understand your question. How can PBI know when use zero or blank? What should be the criteria to hide the row or show zero?

[MadsEmil]

Whether there are any non-blank values in the row. So if both years are shown, the blanks should be shown as zeros, as there are some values in the row that are not blank. When all values in a row are blank however, the row should not be shown. However, if I do a simple if blank then 0 measure, the rows always show up.

[_AAndrade · Accepted solution]

Hi @MadsEmil,

I'm attaching a pbix file with my solution.
I hope this could solve your issue.

[MadsEmil]

Excellent! Thank you very much 🙂

[MadsEmil]

Here's an example of how this might be achieved:

Sum of amount = SUM(Table1[Amount])

Sum w 0 = 
    VAR _Group = MAX(Table1[Group])
    VAR _Subgroup = MAX(Table1[Subgroup])
    VAR _Type = MAX(Table1[Type])
    VAR _Count = 
        COUNTX(
            FILTER(
                ALLSELECTED(Table1),
                Table1[Group] = _Group
                && Table1[Subgroup] = _Subgroup
                && Table1[Type] = _Type
            ),

            [Sum of amount]
        )

RETURN
    IF(
        _Count = 0,
        BLANK(),
        IF(
            ISBLANK([Sum of amount]), 
            0, 
            [Sum of amount])
    )

 However, while this does hide rows with only blank values, it does not show zeros instead of blanks when rows are shown. I hope this cleared up any confusion about my question.

[_AAndrade]

Hi @MadsEmil,

If think the easist thing to do is using two diferent measures.
The actual measure is hidding the row if the results is blank.
So you need another measure like this:
IF( ISBLANK([Your measure]), 0, [Your measure])

[MadsEmil]

Thank you for replying so fast! I'm unsure exactly what you mean. Which measure would I put into the values field of my matrix then?