topic Re: Sum measure based on another measure value in DAX Commands and Tips

Sum measure based on another measure value

tjhoolahan — Tue, 11 Feb 2020 15:24:50 GMT

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

Re: Sum measure based on another measure value

az38 — Tue, 11 Feb 2020 17:33:12 GMT

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

Re: Sum measure based on another measure value

tjhoolahan — Tue, 11 Feb 2020 17:40:53 GMT

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

Re: Sum measure based on another measure value

az38 — Tue, 11 Feb 2020 17:46:09 GMT

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

Re: Sum measure based on another measure value

tjhoolahan — Tue, 11 Feb 2020 17:56:24 GMT

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?

Re: Sum measure based on another measure value

az38 — Tue, 11 Feb 2020 18:06:38 GMT

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

Re: Sum measure based on another measure value

tjhoolahan — Wed, 19 Feb 2020 14:34:58 GMT

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?

Re: Sum measure based on another measure value

az38 — Wed, 19 Feb 2020 17:26:33 GMT

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

Re: Sum measure based on another measure value

tlaurindo — Thu, 20 Feb 2020 14:24:04 GMT

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

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

Re: Sum measure based on another measure value

Anonymous — Sat, 22 Feb 2020 19:57:50 GMT

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

Re: Sum measure based on another measure value

tjhoolahan — Thu, 30 Apr 2020 16:04:04 GMT

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.