Forum Discussion
Measure error to avoid blank pie chart "The function SUM cannot with values of type String"
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", "")
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.
Yep that makes sense. In that case you could also try this:
Measure = IF(ISBLANK(SelectedValue('B-Base Planned'[Expense])), "No Data Available", "")
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?
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?