Forum Discussion
Table expansion measure
This is an example of the model I have. All filters direction across tables are setted as bidirectional in order to trying to get work my measure:
From TypeM table I want to show the SumAmt. The problem is when I'm trying to show the SumAmt by date.
I'm dealing with table expansion but I can't make it work. Reading this book, as Marco and Alberto Pointed "from the farthest table from the fact table to the nearest one", I'm using this measure:
Sum of Amount= CALCULATE( SUM(TypeM[SumAmt]); CALCULATETABLE(TypeM;Calendar;Delegat;Customer;Solution)
)
But I'm not having the expected result:
Having selected Model1 for simplicity, I'm getting this:
How can I get the right total?
Edit: I'm attaching a sample PBIX: This PBIX
Anonymous wrote:
At the end, the expected result in the last table should be:
MonthName| Year | Sum of Amt
Sept 2019 5
Feb 2020 5
Total | 10
Your data model does not look correct to try and get that sort of result.
Your date is linked to the amount through a many to many (m2m) relationship. The typical example of a m2m relationship is a joint bank account. If you have a husband and wife with a joint bank account and there is $10 in that account when you list the account balance by person you would see the following:
Husband $10
Wife $10
Total $10
So the huband and wife cannot both spend $10, if the husband spends $10 there will be 0 left in the account.
This is the way you have modelled your dates against the TypeM table. If you are saying that Model1 had $5 of sales in Sep 19 and $5 sales in Feb 2020. Then there should be a DateID column in the TypeM table and a row for each month against Model1. If you do this then the 2 months will naturally aggregate and you will get a total of $10.
3 Replies
v-xuding-msftCommunity Support
Hi Anonymous ,
Can you please show the expected results? You could reference the blog firstly. Hope it can help you.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reference. The problem is that is not possible to reference from calendar table to the other table with an inactive relation. At the end, the expected result in the last table should be:
MonthName| Year | Sum of Amt
Sept 2019 5
Feb 2020 5
Total | 10
Thanks in advance!
- d_gosbell
Super User
Anonymous wrote:
At the end, the expected result in the last table should be:
MonthName| Year | Sum of Amt
Sept 2019 5
Feb 2020 5
Total | 10
Your data model does not look correct to try and get that sort of result.
Your date is linked to the amount through a many to many (m2m) relationship. The typical example of a m2m relationship is a joint bank account. If you have a husband and wife with a joint bank account and there is $10 in that account when you list the account balance by person you would see the following:
Husband $10
Wife $10
Total $10
So the huband and wife cannot both spend $10, if the husband spends $10 there will be 0 left in the account.
This is the way you have modelled your dates against the TypeM table. If you are saying that Model1 had $5 of sales in Sep 19 and $5 sales in Feb 2020. Then there should be a DateID column in the TypeM table and a row for each month against Model1. If you do this then the 2 months will naturally aggregate and you will get a total of $10.