Divide function with missing values

[qmestu]

Hi

I have the following data:

value	year	category
72175	2011	A
71709	2012	A
70671	2014	A
70369	2015	A
70103	2016	A
69922	2017	A
69828	2018	A
69776	2019	A
69915	2020	A
69850	2021	A
10420	2011	B
10420	2012	B
10420	2018	B

I also have 2 measures:

A=calculate(average(table[value]),table[category]="A")

B=calculate(average(table[value]),table[category]="B")

I need a 3rd measure:

C=calculate(divide([b],[a]))

This works as expected, except when B has no values, the visual that has the C measure shows 0% instead of no value. I've tried setting the alternate result of divide to 0,"" or blank() and nothing seems to work. Ideally it would need to show no value. The years are on a second table, that is connected to the table above with a single direction link.

Thanks.

[Idrissshatila · Accepted solution]

@qmestu ,

did you try to enable the show items with no data on the year field ?

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[Idrissshatila]

Hello @qmestu ,

did you try this C=calculate(divide([b],[a], "No value" ))

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[qmestu]

Even if this would work, it would show "no value" on the chart, which isn't what i need. I need it to show the year but without any value. Thanks.