cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

sum of a calculated measure

I created calculated measure based on two calculated measures .

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 .

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)
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

5 REPLIES 5
New Member

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

HI
Try like this. hope it will help u

Anonymous
Not applicable
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
Anonymous
Not applicable

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.