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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Dax help

Maleehah_0-1630678404235.png

why does this not work

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

Here I will explain to you based on your screenshoot and your code.

From your screenshot we know [Margin] is a measure, you couldn't add a measure into sum. You want to get sum of [Margin] in YTD. Try sumx function. 

[Margin] is based on [Weighted Average Yield] and [Weighted Average COF]. You need to add table name which you used in [Weighted Average Yield] and [Weighted Average COF] in Sumx.

sumx("Data Table Name Used in two basic measures",[Margin])

 

2. TotalYTD is a time intelligence function in Dax code, it's format is as below.

 

Measure = TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])  

 

Please make sure Detail table is a date table with continous date in it. Details[Go Live] column should be a date column.

Make Sure the table you used to calculate [Weighted Average Yield] and [Weighted Average COF] has relationship with Detail table. 

Totak spread YTD = TOTALYTD(SUMX('Table',[Margin]),Detail[Go Live])

 

I build a sample, in my sample key measure table is my data table.

1.png

Relationship:

2.png

Measures:

Weighted Average Yield = AVERAGE('Key Measure'[Value1])
Weighted Average COF = AVERAGE('Key Measure'[Value2])
Margin = [Weighted Average Yield] - [Weighted Average COF]
Totak spread YTD = TOTALYTD(SUMX('Key Measure',[Margin]),Detail[Go Live])

 Result:

3.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

Here I will explain to you based on your screenshoot and your code.

From your screenshot we know [Margin] is a measure, you couldn't add a measure into sum. You want to get sum of [Margin] in YTD. Try sumx function. 

[Margin] is based on [Weighted Average Yield] and [Weighted Average COF]. You need to add table name which you used in [Weighted Average Yield] and [Weighted Average COF] in Sumx.

sumx("Data Table Name Used in two basic measures",[Margin])

 

2. TotalYTD is a time intelligence function in Dax code, it's format is as below.

 

Measure = TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])  

 

Please make sure Detail table is a date table with continous date in it. Details[Go Live] column should be a date column.

Make Sure the table you used to calculate [Weighted Average Yield] and [Weighted Average COF] has relationship with Detail table. 

Totak spread YTD = TOTALYTD(SUMX('Table',[Margin]),Detail[Go Live])

 

I build a sample, in my sample key measure table is my data table.

1.png

Relationship:

2.png

Measures:

Weighted Average Yield = AVERAGE('Key Measure'[Value1])
Weighted Average COF = AVERAGE('Key Measure'[Value2])
Margin = [Weighted Average Yield] - [Weighted Average COF]
Totak spread YTD = TOTALYTD(SUMX('Key Measure',[Margin]),Detail[Go Live])

 Result:

3.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k
Super User
Super User

@Anonymous did you read the link to the post I mentioned in my reply. Once again, you need to explain what you are trying to achieve with sample data. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

ZikoPowerBI
Helper II
Helper II

Please send the Expresision used in Margin and teh screenshot of your datamodel.

This might cause beacuse the Measure Margin that is created does not have association with Detail table.

Anonymous
Not applicable

Margin =
[Weighted Average Yield] - [Weighted Average COF]
 
Maleehah_0-1630682509359.png

 

There is no relation between key Meaures table and Details table , 
ou have to either give the relation of details table in the dax or connect it.

parry2k
Super User
Super User

@Anonymous margin is already a measure and you can not use another aggregation for the measure. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors