Forum Discussion

Rachel_tsao's avatar
Rachel_tsao
Frequent Visitor
3 years ago
Solved

How to show Zero when there is no relation in drilldown level

Hi,Can someone help me to let Customer Target Value (in red frame) show "Zero" in Project level breakdown?

Current :

Expecting result:

 

 

Backgroud: I have one customer with multiple project, but the Target is only in custoemr level, so  I would like to compare the Customer Target vs Project total value in one table with project breakdown, but do not show the target value in project level.

Relationship setting:

Thank you !

 

Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi Rachel_tsao ,

     

    You can try

    =
//if project returns one single value then 0 otherwise the target
IF (
    HASONEVALUE ( table[project] ),
    0,
    [target measure]
)

     

    or

    =
IF (
    HASONEVALUE ( table[customer] ),
    [target measure],
    0
)

    but the first one might be what you're looking for

1 Reply

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    Hi Rachel_tsao ,

     

    You can try

    =
//if project returns one single value then 0 otherwise the target
IF (
    HASONEVALUE ( table[project] ),
    0,
    [target measure]
)

     

    or

    =
IF (
    HASONEVALUE ( table[customer] ),
    [target measure],
    0
)

    but the first one might be what you're looking for