Forum Discussion

tjhoolahan's avatar
tjhoolahan
Icon for Helper I rankHelper I
6 years ago
Solved

Sum measure based on another measure value

I have created a measure for the "live month number" of a client in our DB. This is an integer value that, for any selected month and client, will inform how many months the client has been live with one of our products.

 

I then want to sum the revenue generated by that client for a specific month, in this case number 4, and always return that value, regardless of which month is selected or shown. The code is below:

 

CALCULATE([Revenue],[Live Month] = 4,all('Date'))
 
The issue I'm having though is that the True/False expression is being used as a table filter expression, as per Calculate's requirements, and this isn't allowed.
 
How do I get around this, using only measures? I.e. how do I calculate the revenue for a client in month 4 of its cycle, using the measures "Revenue" and "Live Month"?
 
I have managed to do it by adding values to columns and applying Calculate accordingly but I'm trying to use Measures to restrict the size of the file and not replicate "Live Month" across multiple different tables...
  • tjhoolahan's avatar
    tjhoolahan
    6 years ago

    Anonymous and az38, thanks for your help and apologies for the delay in reply. In the end, attempting to do it through a measure proved too difficult and time consuming, so I simply added a calculated column to the relevant table. I will revisit at some stage in the future, as I believe the calculated column is causing a fairly large strain on my dataset, but, for now, I've delivered against my objective so can leave it for a bit.

10 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi tjhoolahan 

    try

    Measure = 
var _lm = [Live Month]
RETURN
CALCULATE([Revenue],all('Date'),_lm = 4)

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

    • tjhoolahan's avatar
      tjhoolahan
      Icon for Helper I rankHelper I
      6 years ago

      az38 - it still gives me an error, as per the image below. Am I doing something wrong? Are you expecting this to work because you are making it a variable (this is still a relatively new concept in PBI to me)?

       

       
       

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        tjhoolahan 

        sorry. try

        Measure = 
var _lm = [Live Month]
RETURN
CALCULATE([Revenue],filter(all('Date'),_lm = 4))

         

        do not hesitate to give a kudo to useful posts and mark solutions as solution

        LinkedIn

  • tlaurindo's avatar
    tlaurindo
    New Member
    6 years ago

    Probably you just need to make a small fix in the formula and it will work. Try this:

    CALCULATE([Revenue],FILTER([Live Month],4,all('Date')))

    or

    CALCULATE([Revenue],IF([Live Month]=4))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hm... I've been trying to understand what you need... but kill me - no chance.

    Maybe if you were more explicit and give some examples, it would make it digestible.

    Best
    D
    • tjhoolahan's avatar
      tjhoolahan
      Icon for Helper I rankHelper I
      6 years ago

      Anonymous and az38, thanks for your help and apologies for the delay in reply. In the end, attempting to do it through a measure proved too difficult and time consuming, so I simply added a calculated column to the relevant table. I will revisit at some stage in the future, as I believe the calculated column is causing a fairly large strain on my dataset, but, for now, I've delivered against my objective so can leave it for a bit.