Forum Discussion
Dax help
why does this not work
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
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.⚡
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.
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.
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/38490Hi 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
