Forum Discussion

chelsweigandt's avatar
chelsweigandt
Regular Visitor
5 years ago
Solved

Calculated column not working with measures

I have a column in my data called Vendor Stock. I need a caculated column that gives me: Count of Vendor Stock > 0 / Count of All of Vendor Stock.

 

I created two measures:

1) Count of Vendor Stock = count(INVENTORY[Vendor Current Stock])

2) Count of Vendor Stock > 0 = CALCULATE(COUNT(INVENTORY[Vendor Current Stock]),FILTER(INVENTORY,INVENTORY[Vendor Current Stock]>0))

 

I then created a column:

Count of Vendor Stock > 0 / Count of Vendor Stock

BUT it won't let me use a calculate measure in my column for Direct Query.

 

Any suggestions on what I can do to get the calculated column I need?

 

Thanks!

Need Help
  • v-xicai's avatar
    v-xicai
    5 years ago

    Hi chelsweigandt ,

     

    Recommend you use a measure to calculate the percent, like DAX below. And columns are only calculated at data load/refresh.

     

    Percent =
VAR _CountAll =
    CALCULATE ( COUNT ( INVENTORY[Vendor Current Stock] ), ALL ( INVENTORY ) )
VAR _CountLimited =
    CALCULATE (
        COUNT ( INVENTORY[Vendor Current Stock] ),
        FILTER ( INVENTORY, INVENTORY[Vendor Current Stock] > 0 )
    )
RETURN
    DIVIDE ( _CountLimited, _CountAll )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    chelsweigandt , You can not use measure in a calculated column.

     

    You have to create a measure and take care of row context using values or summarize.

  • v-xicai's avatar
    v-xicai
    Community Support
    5 years ago

    Hi chelsweigandt ,

     

    Recommend you use a measure to calculate the percent, like DAX below. And columns are only calculated at data load/refresh.

     

    Percent =
VAR _CountAll =
    CALCULATE ( COUNT ( INVENTORY[Vendor Current Stock] ), ALL ( INVENTORY ) )
VAR _CountLimited =
    CALCULATE (
        COUNT ( INVENTORY[Vendor Current Stock] ),
        FILTER ( INVENTORY, INVENTORY[Vendor Current Stock] > 0 )
    )
RETURN
    DIVIDE ( _CountLimited, _CountAll )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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