cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Variance Calculation Not Working (Error thinks there is text)

Good morning

 

Another newbie here...

 

I've started building some financial reporting dashboards in Power BI and want to show the actual vs forecast result for a given month, but when I insert a measure to do this (CYActvsForecast= [CYTotal] - [FcastTotal]), the visual won't display as it thinks there are text values in there somewhere. 

Jillith_1-1596017819451.png

 

As you can see fromt the screenshot below all the values in CYTotal and FcastTotal are numerical, so I don't know what is causing this error! Has anyone seen anything like it before or have any idea how I would go about troubleshooting this?

Jillith_0-1596017650559.png

Many thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Variance Calculation Not Working (Error thinks there is text)

@Jillith , You are trying to use sum on a text datatype. Please change the data type and try . Or change in power query

 

Data Format New Rib.png



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Variance Calculation Not Working (Error thinks there is text)

@Jillith , You are trying to use sum on a text datatype. Please change the data type and try . Or change in power query

 

Data Format New Rib.png



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Frequent Visitor

Re: Variance Calculation Not Working (Error thinks there is text)

Thanks for getting back to me. I forgot to mention that both fields used in the variance calculation are measures - the format section does say it is text but all options are greyed out so I can't change anything...

 

Jillith_0-1596021854536.png

 

Highlighted
Frequent Visitor

Re: Variance Calculation Not Working (Error thinks there is text)

OK - I've got this working by adding value () around my measures used in the variance calculation after searching on why measures are formatted as text/how to change them.

 

I couldn't find a definitive answer on why this was happening but it's solved now so thank you. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors