Forum Discussion

mejiaks's avatar
mejiaks
Frequent Visitor
7 years ago
Solved

Calculated() with Var and literal filter

Hi

 

I am struggling with a measure that need to show a sum of orders based on a filter

when i specify the filter value with a literal like this

 

Orders 2018 with Literal = CALCULATE(sum(orders[qty]),FILTER(orders,orders[year]=2018))
 
the result show as spected
 
but when i specify the filter base on a value selected from a table, like this
 
Orders 2018 with Measure = CALCULATE(sum(orders[qty]),FILTER(orders,orders[year]=[Year # 1]))
 
being [Year # 1] a measure 
 
Year # 1 = IF ( HASONEVALUE ( 'Year # 1'[Year] ), VALUES( 'Year # 1'[Year]), 2018)
 
it does not work
 
see image below
 
 
i cannot work with literals cause I need to compare two years selected by the user and calculate difference between the two measures 
 
any ideas will be appreciatted
 
I would upload the file but i don't see the option
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    mejiaks 

     

    Try this one...We need to take measure out of FILTER function

     

    Orders 2018 with Measure =
VAR myyear = [Year # 1]
RETURN
    CALCULATE ( SUM ( orders[qty] ), FILTER ( orders, orders[year] = myyear ) )

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    mejiaks 

     

    Try this one...We need to take measure out of FILTER function

     

    Orders 2018 with Measure =
VAR myyear = [Year # 1]
RETURN
    CALCULATE ( SUM ( orders[qty] ), FILTER ( orders, orders[year] = myyear ) )
  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi mejiaks 

    As tested, Zubair_Muhammad's suggestion should be helpful.

    If it doesn't solve your requirement as said below.

    "compare two years selected by the user and calculate difference between the two measures "

    you could take my advice as below.

     

    create a new table

    Table2 = VALUES(Sheet7[year])

    don't create relationships among three tables.

     

    create measures

    Year_1 = IF ( HASONEVALUE ( Table1[year] ), VALUES( Table1[year]), 2018)

Year_2 = IF ( HASONEVALUE ( Table2[year] ), VALUES( Table2[year]), 2018)

Measure1 = 
VAR myyear = [Year_1]
RETURN
    CALCULATE ( SUM (Sheet7[qty] ), FILTER ( ALL(Sheet7), Sheet7[year] = myyear ) )

Measure2 = 
VAR myyear = [Year_2]
RETURN
    CALCULATE ( SUM (Sheet7[qty] ), FILTER ( ALL(Sheet7), Sheet7[year] = myyear ) )

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.