Sum measure based on another measure value

[tjhoolahan]

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...

[Anonymous]

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 · Accepted solution]

@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.

[tlaurindo]

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))

[az38]

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]

@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]

@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

[tjhoolahan]

@az38 , thanks, I no longer get the error.

However, I now don't get the correct output in the table. In image below, you'll see the value of "Adtech Revenue" in month 4 is 10060, whereas the "3m Gate (measure based approach)", which is the code you supplied, returns 61518.81 (I believe this is the total revenue ever) and only in month 4, not the rest.

Can we a) somehow amend the measure so it returns the correct month 4 value? b) amend the measure so it returns the month 4 value in all months?

[az38]

@tjhoolahan 

maybe you should just try to IF() in return?

Measure = 
var _lm = [Live Month]
RETURN
if(_lm=4,[Revenue],BLANK())

?

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

[tjhoolahan]

@az38 - almost there. Only thing required now though is to be able to see that value (set as the month 4 value) for all months. In the image below, I've circled the dates which are currently blank that should be filled with 10400.54. Any ideas how to rectify this situation?

[az38]

@tjhoolahan 

not sure I understand you correct, but maybe this

Measure 2 = calculate('Adtech Revenue'[Adtech Revenue], filter(ALL('Adtech Revenue'),'Adtech Revenue'[Adtech Live Month]=4))