Forum Discussion
Helper IVDivide function with missing values
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.
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 👍
3 Replies
- Idrissshatila
Super User
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 👍
- 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 👍
qmestuEven 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.