topic Re: sum of a calculated measure in DAX Commands and Tips

sum of a calculated measure

Anonymous — Wed, 26 Feb 2020 11:34:17 GMT

I created calculated measure based on two calculated measures .

previous_year_revenu = CALCULATE(SUM(Table[REVENUE]);SAMEPERIODLASTYEAR(Table[BUSINESS_DATE].[Date]))

previous_year_exchange_rate =CALCULATE([Exchange_rate];SAMEPERIODLASTYEAR(Table[BUSINESS_DATE].[Date]))

I am getting the SUM of that calculated measure 'new_revenue' wrong. I couldn't figure it out how to write an expression to SUM the calculated measure using DAX even after looking into forums.
Can any one help me with an expression to find the SUM of a calculated measure.

here expected sum is 2000 ( 200+1800 ) but value is coming like 3200 which 4000 * 0.8 . but i need exact sum .

someone please help me??

sample data :

previous_year_revenue	previous_year_exchange_rate	new_revenue = (previous_year_revenue) * (previous_year_exchange_rate)
1000	0.2	200
3000	0.6	1800
sum = 4000	sum = 0.8	sum = 4000*0.8 = 3200 ( expected sum = 2000)

Re: sum of a calculated measure

Anonymous — Wed, 26 Feb 2020 11:53:26 GMT

Hi

You have to iterate the units of time with SUMX (probably years) but I can't tell you more than that because I don't have enough information about the model and underlying assumptions. If your revenue measures are only relevant for full years, then you should make sure they are calculated only when there's a full year in view.

By the way, I can immediately see you're not designing the model the way it should be. If you want to listen... Please create a proper Date table in the model and stop relying on the automatic behaviour of PBI. Unless you want to have problems later. A proper Calendar dimension should always be present in a model that uses time calculations.

The naming (if that's final) is also something you should pay a lot of attention to. Naming a table 'Table' is not particularly helpful.

SUM(Table[REVENUE]) should be a measure itself. You should build more complex measures from simpler ones, not from ground up.

Also, bear in mind that fact tables should be hidden totally from view and slicing should be done via dimensions only.

But... You do as you please, of course.

Best
D

Re: sum of a calculated measure

Anonymous — Wed, 26 Feb 2020 12:50:39 GMT

table is a dummy name i used here and fact table are hidden from the slicer . i use only dimension slicer. i agree with ur suggestions, i need previous record of date as well , thats y i gave date there. currently i need sum of new_revenue .please help me to write that dax.

Re: sum of a calculated measure

Anonymous — Wed, 26 Feb 2020 13:07:06 GMT

HI
Try like this. hope it will help u

Re: sum of a calculated measure

Anonymous — Wed, 26 Feb 2020 13:50:37 GMT

I did say I can't because I don't know the model. Please take a s/shot of it and paste here. If you only want this to work for VISIBLE YEARS, then you'll need something like this:

sumx( values( Table[Years] ), [new revenue] )

Best
D

Re: sum of a calculated measure

Anonymous — Mon, 17 Apr 2023 17:21:53 GMT

Hi,

I am trying to figure out how to create a YTD SUM for a visual built from a calucated measure. The Sum I would like to see is S/S Variance YTD, can someone help me?

See snipshot below.