Please help! What is wrong with this measure?

[BTA999]

Hi,

I have been wrestling with this for some time and just cannot figure out what is happening.

I keep getting the not a number (NaN) error when I try to put my measures into a (stacked bar chart) visual.

This is measure is to calculate the change of market share (MS) of our sister companies between 2018 and 2020.

The example is about the company coded "ERX".

This is the formula:

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

The query is called 'Total Sales 2018-21' and a screenshot of it is below.

Now, I checked to see that there are no blanks and no nulls in either columns.

There is a division but as there are sum figures involved (and they are not zero any of them) this should also be fine.

Also, there are no infinite numbers either.

The columns are appropriately formated, as you can see in the screenshot.

So, I started cheking the formula with the ISERROR function.

I took my formula into 2 parts and checked them separately.

The first part:

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

checks out fine and produces a valid figure (16%).

The second part however:

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

returns a blank figure showing that there is a problem.

My dilemma is that these 2 formulas are exactly the same, the only difference being the column they take the figures from. This means, the problem is with the "Sales 2018" column of my query. But what is it?

Could somebody please help me find the problem because I am at my wits end!

Thanks a million!

The screenshot of my query called Total Sales 2018-21:

[johnt75 · Accepted solution]

Try using the DIVIDE function instead of the / operator.

[johnt75 · Accepted solution]

Try using the DIVIDE function instead of the / operator.

[BTA999 · Accepted solution]

Hi johnt75,

Thank you for your prompt reply, I do appreciate it!

Your solution worked!

Feeling a bit idiot, what a newbie I am but anyway this is OK now.

Of course it opened up another issue but I will start a new thread about that.

Thx again!