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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Measure with sum and fixed value

Hi all,

 

I have issues with two of my current formulas:

 

1) Co2 from electricity = Electricity (mwh) x market based factor 

The market factor is based on the country, therefore the value different per plant. For example, for Q1 it should be 891 x 0.4 = 356.4 , but instead it is taking the sum of 0.4 (1.19) and multiplying (to then equal 1058.51), when I need the market factor to remain a fixed value when multiplying. 

 

Currently I have the following measures:

WRONG Market Factor = SUMX('RAW Data','RAW Data'[Market based FACTOR]
WRONG CO2 from Electricity = SUMX('RAW Data','RAW Data'[Electricity (MWh)])*'RAW Data'[WRONG Market Factor]
 

2) Co2 from combustion = Total combustion x Factor Natural Gas

The same issue here, except the factor needs to always remain the same. Should be 811 x 0.2 = 162.2 (but instead the answer is coming out as 491.22).

 

Currently I have the following measure:

co2 from combustion = Sumx('RAW Data','RAW Data'[Total Combustion]* Sum('RAW Data'[FACTORS Natural Gas lower calorific value lcv (MWh)]))
 

Lola_22_0-1642671918456.png

I would appreciate any help or advice, thanks in advance! 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Anonymous Not sure I completely understand this. It looks like you have a table visual with the correct numbers in it. Where do those come from? Can you not just use those in your formula? If the second and third columns are columns, just use the same aggregation for those as in the table visual. For example, if they are default Sum aggregation, use SUM([Market based FACTOR]) or if they are Max, use MAX([Market based FACTOR])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@Greg_Deckler thanks so much, your advice worked! I needed to use "max" in place of "sum" for the factors and they reamined at a fixed value. 

Greg_Deckler
Super User
Super User

@Anonymous Not sure I completely understand this. It looks like you have a table visual with the correct numbers in it. Where do those come from? Can you not just use those in your formula? If the second and third columns are columns, just use the same aggregation for those as in the table visual. For example, if they are default Sum aggregation, use SUM([Market based FACTOR]) or if they are Max, use MAX([Market based FACTOR])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.