Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

sum of a calculated measure

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_revenueprevious_year_exchange_ratenew_revenue = (previous_year_revenue) * (previous_year_exchange_rate)
10000.2200
30000.61800
sum = 4000sum = 0.8sum = 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

View solution in original post

5 REPLIES 5
marissabru
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. 

marissabru_0-1681752082581.png

 

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

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.