How to show zeroes in a matrix (+0 is not working)

[orlandom]

Hi,

I'm trying to show percentages and integer values in a matrix. I'm using the following measure to format the results:

Format for Summary = 
IF(HASONEVALUE(Summary_Region_Price[Category]),
	IF(VALUES(Summary_Region_Price[Category]) = "Percent",
		FORMAT(SUM(Summary_Region_Price[Total]), "0.0%"),
		FORMAT(SUM(Summary_Region_Price[Total]), "0")
	   )
)

Obviously, the measure above is showing BLANK when the results are blank. So I tried this:

Format for Summary = 
IF(HASONEVALUE(Summary_Region_Price[Category]),
	IF(VALUES(Summary_Region_Price[Category]) = "Percent",
		FORMAT(SUM(Summary_Region_Price[Total]) + 0, "0.0%"),
		FORMAT(SUM(Summary_Region_Price[Total]) + 0, "0")
	   )
)

But is still showing blanks...

Any ideas?

[v-chuncz-msft]

@orlandom,

Add a calculated table and create relationship on [Region], then drag [Region] from this table to Columns. You'll find the difference for HASONEVALUE ( Summary_Region_Price[Category] ).

Table =
DISTINCT ( Summary_Region_Price[Region] )

[orlandom]

@prateekraina, Thanks a lot for the time you put into this.

@v-chuncz-msft, I didn't think about this, this is brilliant. Now I understand why I have my problem: Since the table is empty, HASONEVALUE is returning BLANK.

Unfortunately, I still don't know how to solve my problem, since I need to access the Category from the matrix to format the zero.

I was thinking something along the lines of:

Format for Summary Table = 

VAR H1V = HASONEVALUE(Summary_Region_Price[Category])
VAR Total = SUM(Summary_Region_Price[Total])
VAR Final_Total = IF(ISBLANK(H1V), 0, IF(H1V, Total, BLANK()))
RETURN

IF(SELECTEDVALUE(Summary_Region_Price[Category]) = "Percent",
    FORMAT(Final_Total, "0.0%"),
    FORMAT(Final_Total, "0")
)

Obviously, this approach doesn't work since SELECTEDVALUE is returning blank when the table is empty.

[orlandom · Accepted solution]

Got it!!!!

I was able to solve the issue with this formula:

Format for Summary Table = 

VAR H1V = HASONEVALUE(Summary_Region_Price[Category])
VAR Total = SUM(Summary_Region_Price[Total])
VAR Final_Total = IF(OR(ISBLANK(H1V), ISBLANK(Total)), 0, IF(H1V, Total, BLANK()))
VAR Current_Category = CALCULATE(SELECTEDVALUE(Summary_Region_Price[Category]),
                                 ALLEXCEPT(Summary_Region_Price, Summary_Region_Price[Category]))
RETURN

IF(Current_Category = "Percent", FORMAT(Final_Total, "0.0%"), FORMAT(Final_Total, "0"))

Thanks for your time @v-chuncz-msft and @prateekraina!!!

[McCow]

Hi all!

It is very interesting theme for me to.
But after implementation this formula I was not be able to return zero instead blank:
Left table - before, right - after implantation:

what the guess?

[prateekraina]

@MC

You missed the step mentioned by @v-chuncz-msft.

"

Add a calculated table and create relationship on [Region], then drag [Region] from this table to Columns. You'll find the difference for HASONEVALUE ( Summary_Region_Price[Category] ).

Table =
DISTINCT ( Summary_Region_Price[Region] )

"

[McCow]

 @prateekraina

i haven't unfortunately understand how this step help to resolve blank cells in the table. May be can you show on real example THIS?

Thank you in advance.

[prateekraina]

You should comare using BLANK(). Something like this:

IF( Summary_Region_Price[Category] = BLANK(), Do This, Do That)

It is not actual code. Just giving you context of how to use BLANK(). 

[orlandom]

Thanks @prateekraina,

Tried this and is not working:

Format for Summary Table = 
  IF(HASONEVALUE(Summary_Region_Price[Category]), 
	 IF(VALUES(Summary_Region_Price[Category]) = "Percent", 
	  IF(SUM(Summary_Region_Price[Total]) = BLANK(), "0.0%", FORMAT(SUM(Summary_Region_Price[Total]), "0.0%")), 
	  IF(SUM(Summary_Region_Price[Total]) = BLANK(), "0", FORMAT(SUM(Summary_Region_Price[Total]), "0"))
	    )
     )

[prateekraina]

Can you provide some 10 sample rows of your data.

[orlandom]

The data is shown below and for the purpose of the measure you can assume the name of the table is Summary_Region_Price.

The structure of the matrix is as follows:
Rows: Category, Price Range
Columns: Region
Values: The measure I showed in the question 

Category	Price Range	Region	Total
Percent	PR1	R1	0.062827
Percent	PR1	R2	0.085616
Percent	PR1	R3	0.054533
Percent	PR1	R4	0.017602
Percent	PR2	R1	0.128272
Percent	PR2	R2	0.226027
Percent	PR2	R4	0.044004
Total	PR1	R1	24
Total	PR1	R2	50
Total	PR1	R3	80
Total	PR1	R4	16
Total	PR2	R1	49
Total	PR2	R2	132
Total	PR2	R4	40

[prateekraina]

Thanks.

Can you give me data for below scenario also as you mentioned earlier:
"Obviously, the measure above is showing BLANK when the results are blank"

I am not getting blank in matrix as per the data you provided.

[orlandom]

Put the Price Range column in the Rows section of the matrix and expand the rows. You should have blanks for the price range PR2 and the region R3.

See below:
Blanks in Matrix

[orlandom]

@prateekraina, any updates?

Appreciate very much your help.

[prateekraina]

I was on it whole night buddy. No work around yet 😞