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

[BTA999]

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:

IMG001

IMG002:

IMG002

[amitchandak]

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

[BTA999]

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.

[v-chenwuz-msft]

Hi @BTA999 ,

You get some values are infinity

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.

[BTA999 · 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