Automatic Total to show blank when one of the rows is blank

[TeresaV]

Hi

I am really stuck with this, I kindly request help 

How can I change the Total that is automatically display in PowerBI? 

Thanks !! 

@amitchandak @Greg_Deckler @grey @parry2k @ktom @TomMartens 

[v-shex-msft]

Hi @TeresaV,

I'd like to confirm what type of blank results show on your visuals:

1, These blank parts mean these records are stored in your table but they did not have values.

2, These blank parts mean the missed records that not include in your table.

For these scenarios, you can check the below suggestions:

#1, You can extract the count of each category with values and compare it with the total count as conditions and use them in if statements.

#2, You may need to get the raw category amount before the calculation, then you can use the available category count to compare with the 'category amount multiple with date label amounts'.

Clever Hierarchy Handling in DAX - SQLBI

Regards,
Xiaoxin Sheng

[parry2k]

@TeresaV it is not that straightforward, send a sample pbix file (remove sensitive information) and I will get back with the solution.

[TeresaV]

I don't have a powerbi file that I can Share with you.. I already tried different ways to solve this issue, but nothing seems to work. I would really appreciate your help on this

[parry2k]

@ashleyfiore good try but it will not work because Value is going to be number and "Date N/A" as text and it will return variant which doesn't work. Also, @TeresaV  need Data N/A on the Total if one of the Value is missing (if I understood it correctly)

[ashleyfiore]

Use this measure:  COALESCE([Your Measure], "Data N/A")

[TeresaV]

This did not work...

[ashleyfiore]

You may need to us the SUMX or COUNTX like shown below. 
COALESCE(SUMX( 'Table' , [Your Measure]), "Data N/A")

Seeing the measure you are using would be helpful if this isn't what you are looking for. 

[TeresaV]

Still not working..

Growth=
var a=MeasureCY
var b=MeasurePY
return
if(a="Data N/A" || b="Data N/A", "Data N/A", if(b=0, "Error", ((a-b)/b)))

[ashleyfiore]

You are replacing the blank value with text in the Measure CY and Measure PY so you will need to create another measure that does not do this to see the result you want. 

I suspect the measure you are using actully looks more like thie:
Growth=
var a = COALESCE( MeasureCY,"Data N/A")
var b = COALESCE( MeasurePY,"Data N/A")
return
if(a="Data N/A" || b="Data N/A", "Data N/A", if(b=0, "Error", ((a-b)/b)))

but should look like this:
Growth  =
var a = MeasureCY,
var b = MeasurePY
return
IF(ISBLANK(a) || ISBLANK(b), "Data N/A", IF(b = 0, "Error", ((a-b)/b)))

[TeresaV]

Your solution doesn't work.. 

In the Measure CY/PY I am saying that if the value is blank it should be replace with "Data N/A"

[parry2k]

@TeresaV there are many ways to do this but quick question, why do you want to suppress the total in Jan and not in Feb, what is the logic behind this?

[TeresaV]

Small correction:

It should be Data N/A if the row is blank or text , like in the image above