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

Bars disappear in my visual (Chart data contains not a number values)

Hi,

 

I am running into this situation and whatever I have tried, can't solve it - but I am quite new to PBI so it might just be something simple. At least I hope it is.

 

I have a databse of sales figures of some of our sister companies from the whole country for the years of 2018, 2019, 2020 and 2021.

I am trying to build a visual that shows the market share (MS) change of these companies between 2020 and 2018. I need to put this on a clustered bar chart to see the negative and positive bars as well.

The query ('Total sales 2018-21') has these columns: City/Province/Partner/Sales 2018/Sales 2019/Sales 2020/Sales 2021 - all appropriately formatted.

 

So, first I created measures to calculate the MS change for all the partners individually.

This is the formula for each:

 

MS LC 2020-18 = 
(CALCULATE(
    SUM('Total Sales 2018-21'[Sales 2020]),'Total Sales 2018-21'[Partner]="LC") / 
SUM('Total Sales 2018-21'[Sales 2020])) - 
(CALCULATE(
    SUM('Total Sales 2018-21'[Sales 2018]),'Total Sales 2018-21'[Partner]="LC") / 
SUM('Total Sales 2018-21'[Sales 2018]))

"LC" here is the code for one of the companies, that changes in the measures for every company.

 

Next, I start to put these into the X axis of the visual and they appear nicely as you can see below in IMG 001.

 

Then, the problem comes when I put the Partners field (from the same query) into the Y axis, as I want to see the names of the companies rather than the legend.

Then the names of the companies appear but the bars disappear, I get some numbers (I can't see where they are coming from) and on the top, there is the big I button that shows me the NaN error. Pls see IMG002 below.

 

I just can't get my head around this. How can there be no numbers? There are numbers all right, it worked with them before the inclusion of the Partners field. So what has changed?

 

Could someone please point me to the right direction?

Thx a million!

 

IMG001:

IMG001IMG001

 

IMG002:

IMG002IMG002

1 ACCEPTED SOLUTION

Hi @v-chenwuz-msft ,

 

Thank you for your reply.

I am not sure what the issue really was but using the DIVIDE function instead of the "/" operator solved the issue.

I just forgot to close this thread, so sorry.

Thank you for your kind help and have a great day!

Attila

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@BTA999 , seem like in one of the measures there is divide by 0 error or data type in the number (or not returning number)



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hi amitchandak,

 

Thank you for the prompt reply.

 

I have thought of this too once I did some reading into the NaN error.

But I can't see how would this be possible.

First, these are all aggregated sales figures from whole years. None of them are 0. There is no division by 0.

Plus, it did calculate the MS changes nicely and showed them BEFORE I include the Partners field.

All this wouldn't happen if there was any problem with the numbers, would it?

To my logic the problem might be with the Partners field, just can't see what.

Hi @BTA999 ,

 

You get some values are infinity

vchenwuzmsft_0-1657002007728.png

 

Perhaps you could try editing the x-axis range of this bar chart so that the vision can focus on the categories that have normal data.

 

Or you can share your pbix and remove the sensitive data.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-chenwuz-msft ,

 

Thank you for your reply.

I am not sure what the issue really was but using the DIVIDE function instead of the "/" operator solved the issue.

I just forgot to close this thread, so sorry.

Thank you for your kind help and have a great day!

Attila

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors