SUM from another table gives wrong number even with relationship

[rssilvaba]

Hi All,

I have the following data in "Plans" Table

MarketId	Month	Year	Requests
34	12	2020	11
34	4	2020	6
34	2	2020	12
34	8	2020	18
34	9	2020	20
34	4	2020	14

And in the "Requests" Table

Created	MarketId
2020/09/03	34
2020/12/04	34
2020/12/10	35

I already have a relationship from both to a separeted table for the market names but I get the wrong results. the data in the table is more but for this example I tried to limit the data just to show.

So, how can I get the number of Requests from table "Plans" based on the month/year and the market? in this case the sum value I get should be 31 for marketid "34"

[amitchandak · Accepted solution]

@rssilvaba , Second table has date, The first on can date

Date = Date([year], [Month],1)

I am nor sure how they are related now. But they can either join on the date columns . or join to common date table .

Can you share data model and expected output

[rssilvaba]

Thanks @AllisonKennedy, Creating a calendarauto table and using @amitchandak solution worked for me. the only drawback for that is if we need to drilldown to the days, but we not doing that with this report so it should be fine.

Thanks all.

[AllisonKennedy]

Glad it's working! You should be able to get it working for days if/when needed with a DimDate table, so let us know how you get on if that becomes a requirement.

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[AllisonKennedy]

Which month/year gives 31? How have you created your relationships? It sounds like you are not accounting for the month/year in the relationships so that may be part of the problem. We need a bit more info to help out here please - screenshot of model view would be a good start. https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html

[rssilvaba]

Hi @AllisonKennedy I tried to reference that columns to the created date. But I am not sure I know how. If I try to refer to a diferent table column as in the DAX below using calculate and filter it does not allow me to refer to a diferent column.

To @lkalawski, sorry I am not that experienced with DAX or PowerBI so I have no idea how I can intersect the data to get the matches I need. If you can point me to some examples I can try to apply them to my problem.

[AllisonKennedy]

@rssilvaba can you please share screenshot of your model view and specify which columns are being used for the relationships?

Also, if you are wanting info by month, you should have a DimDate table - do you have one of these yet?

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[lkalawski]

Hi @rssilvaba

If you only have a relationship to market name, then there is no date differentiation, so you see more values than you should.

Proposed solutions:
1. You can create a concatenate key in both tables which will consist of market, year and month - example: 34202012. And then you will join both tables by these keys.
2. If you have a relation, you need to create a measure:

Calculate (Sum (Plans [Requests]), MONTH (Created) = Plans [Month], YEAR (Created) = Plans [Year])

_______________
If I helped, please accept the solution and give kudos! 😀