Measure error to avoid blank pie chart "The function SUM cannot with values of type String"

[jessmc64]

I am creating a dashboard with various different pie charts that will be refreshed periodically throughout the year. I have one pie chart that currently has no data available and is therefore appearing as a blank space on my dashboard. People think there is an error with my dashboard, but there just hasn't been any of this type of spending yet. Eventutally, a pie chart will appear when that type of spending happens, but I'd like there to be a message to make it clear that this space is purposly blank at the moment.

I have followed instructions from various threads on here to create a measure using a card visual that should display "No data available" instead of a blank space. Every time I try to select the card I get "Error fetching data for this visual" "Calculation eorror in measure 'B-Base Planned'[Measure]: The function SUM cannot work with values of type string. "

The measure syntax I have used from exmaples in these forums is below: 

Measure = IF(ISBLANK(SUM('B-Base Planned'[Planned])), "No Data Available", "")

It accepts the measure, but then says error fetching data. What am I doing wrong?

[v-pagayam-msft]

Hi @jessmc64 ,
As your query got resoved.consider accepting the solution that helped you as accept as solution.

Thank you.

[v-pagayam-msft]

Hi @jessmc64 ,
Glad that your first query got resolved.Consider marking the answer that helped you as accept as solution.

Thank you.

[v-pagayam-msft]

Hi @jessmc64 ,
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.If our answer resolved your query, please mark it as "Accept Answer" and select "Yes" if it was helpful.If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[d_m_LNK]

Since you are trying to sum the blank data that function doesn't know what to do.  You could take out the Sum function and it may work:
Measure = IF(ISBLANK('B-Base Planned'[Expense]), "No Data Available", "")

[jessmc64]

Tried this and got a new error "A single value for column 'Planned' in table 'B-Base Planned' cannot be determined. This can happen when a measure formula refers to a colum that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

*I did cahnge my formula to 'Planned' instead of 'Expense' , just realized I was using the wrong data field but still has the same issue*

This new error makes sence. I am trying to make a pie chart with each slice representing a different type of planned spending (e.g. office furnature, training, travel, etc.). I have 14 different spending types in my legend, which all currently sit at $0. I don't want any aggregation like SUM or COUNT, because the numbers I'm pulling from Excel are already totals. I just want there to be some indicator that there has been no spending, until a number is added to one of those rows in my Excel sheet.

[d_m_LNK]

Yep that makes sense.  In that case you could also try this:

Measure = IF(ISBLANK(SelectedValue('B-Base Planned'[Expense])), "No Data Available", "")

[jessmc64]

Thank you so much for your help I'm so colse!!

The visual is showing up saying "No Data Available". Now all I need is for it to disappear when there is data. I tested this by updating my linked speadsheet with some numbers in the 'Planned Spending' rows. When I hit refresh my pie chart appeared, but the "No Data Available" card is still on top. How do I make it automatically go away when there is data?

[v-pagayam-msft]

Hi @jessmc64 ,
Thank you @d_m_LNK  for the accurate response!

Thank you for your question! To ensure that the "No Data Available" message disappears automatically when data is present, you may use the following measure :

Measure = IF( ISBLANK(SUM('B-Base Planned'[Planned])), "No Data Available",BLANK() )

ion.

Thank you for being a valued member in Microsoft Fabric Community Forum!

If it did not resolve your query,As d_m_LNK suggested please raise new thread about stacking visuals.As measure is fixed ,please mark the solution as accepted.

[jessmc64]

The card now says "BLANK" instead of "No Data Available" and did not disppear when data became available unfortnuately 

[v-pagayam-msft]

Hi @jessmc64 ,
Thank you for your patience!
Since the measure is returning blank,try using the below query

Measure =
VAR PlannedSpending = SUM('B-Base Planned'[Planned])
RETURN IF(NOT ISBLANK(PlannedSpending), BLANK(), "No Data Available")

Also,check if the card visual have a "Show blank as" option set to display text (like "BLANK").

I hope this helps.If so,consider accepting it as solution.

[jessmc64]

This didn't work, nothing changed. I'm probably gonna give up on this. 

[v-pagayam-msft]

Hi @jessmc64 ,
Thank you for the patience.I'm sorry to hear that it didn’t work as expected. Please raise a new thread to get it resolved,we are happy to help you further.
Since your first query was resolved, please consider accepting the initial helpful reply as the solution. This will help others who has similar issues.

Thank you.

[d_m_LNK]

That sounds like a separate issue than the measure.  It sounds like you are stacking two different visuals on top of one another?  This might be a good post for a new thread maybe.  If the measure is fixed can you mark the solution and post a new thread about stacking visuals when blank?