Forum Discussion

jeanL's avatar
jeanL
Icon for Helper I rankHelper I
4 years ago

set constant line based on formula

Hi

 

I am trying to create two constant line:

1. based on main service average rate

2. based on peer / customer segment average rate

 

and these should not be affected by the filter i apply. 

 

I tried for item 1 the following

The output stays constant regardless on vessel direction filter and updated according when i change main service filter.

 

But this gets affected once I run filters on the slicers for customer segment and control party.  Is there a separate filter i should add on the syntax?

 

trade average = 
VAR t_ave =
    CALCULATE(
        [Rate],
        ALL(summary_table[Main Service], summary_table[vessel_direction]))
RETURN
    t_ave

 

Need Help
Show and Tell

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    jeanL , In Visual format pane, In analytics pane you have an option for constant line, there you have an option for constant line, Ine new version you can give measure using fx. Try that out

     

    Here I have used Margin % both as value and as constant line(Taken overall value)

     

    • jeanL's avatar
      jeanL
      Icon for Helper I rankHelper I
      4 years ago

      HI amitchandak 

       

      I can set the constant line but how do i set it where it will not be affected by filter?

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi jeanL 

    It is OK for [trade average] to use All() function, but the value returned is also affected by [Rate], you need to check the context of [Rate]. 

    For example, it works ok in the first picture, but if I set 'Table'[Column1]="a" in the second picture, then function All() in measure [test] won't work as expected.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.