Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Using Measure Value as a if condition in another measure?

Hello,

 

I'm working with a 2gb semantic model and I was hoping to avoid creating a calculated column for the sake of performance.  Appreciate you guy's help in advance.

We are a large retailer with 1,000 stores.  I'm like to create a measure that will do a distinct count of the offices that archieved Net Income greater than > 15%.

I have the following data table that looks like the following:

 

and Net Income % is calculated using the following:
***
NOI% Measure =
VAR Revenue=Calculate(sum(Amount),Attribute="Revenue")
VAR NetIncome=Calculate(sum(Amount),Attribute="Net Income")

Return
Divide (NetIncome,Revenue,0)
***


What I'm trying to achieve is a measure that will give me an Store Count, if NOI % is greater > 15%  (nicknamed Performing Store)

I can achieve this using calculated columns.  However, there are 12 months x 10 years x 1000 stores.. I'll need to create a new table and it will add massive size to my table and increase lag.

Is there a way I can write  a measure, using a filtering criteria from another measure?

In my example above, something like this:

Performing Store Count = calculate(DistinctCount(StoreNumber),filter(NetIncome%>.15))  ?

My apologies if my example is not making sense.


Need Help

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Anonymous - create NOI% Measure as like below and replace with your table name

     

    NOI% Measure =
    VAR Revenue = CALCULATE(SUM('Sales'[Amount]), 'Sales'[Attribute] = "Revenue")
    VAR NetIncome = CALCULATE(SUM('Sales'[Amount]), 'Sales'[Attribute] = "Net Income")
    RETURN
    DIVIDE(NetIncome, Revenue, 0)

     

    create another measure to get the Store Count 

     

    Performing Store Count =
    CALCULATE(
    DISTINCTCOUNT('Sales'[StoreNumber]),
    FILTER(
    SUMMARIZE(
    'Sales',
    'Sales'[StoreNumber],
    "NOI%", [NOI% Measure]
    ),
    [NOI%] > 0.15
    )
    )

     

    Hope it works as per expectations.

  • sroy_16's avatar
    sroy_16
    Resolver II
    2 years ago

    Hello Anonymous 

     

    Try using this measure and see if it works.

     

    Performing Store Count =
    VAR NetIncomeThreshold = 0.15
    RETURN
    CALCULATE(
    DISTINCTCOUNT('YourTable'[StoreNumber]),
    FILTER(
    ALL('YourTable'),
    [NOI% Measure] > NetIncomeThreshold
    )
    )

     

    Did I answer your question? Mark my post as a solution!
    If I helped you, click on the Thumbs Up to give Kudos.