Dax help

[Anonymous]

why does this not work

[v-rzhou-msft · Accepted solution]

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.

[parry2k]

@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

[ZikoPowerBI]

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]

Margin =

[Weighted Average Yield] - [Weighted Average COF]

[ZikoPowerBI]

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]

@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.⚡