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(order...
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 ) )
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.