cancel
Showing results 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

Anonymous
Not applicable

## Dax help

why does this not work

1 ACCEPTED SOLUTION
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.

Relationship:

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:

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.

6 REPLIES 6
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.

Relationship:

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:

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.

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.

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.

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]

Helper II

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.

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.

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.

Announcements

#### Power BI Monthly Update - July 2024

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

#### 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
Top Kudoed Authors