Show all data from table2 in a visual in two many to many related tables

[Anonymous]

I have a key column in two many to many related table and sample representation of data is -

(attaching sample version of the table to get the point across as there are other numersous columns not contributing to this visual)

table 1

table 2

I am making a line graph with date on x axis and the value1 and value 2 on y-axis. The value1 is true for all dates. It is basically a standard target value. Now I want all the value1 summed up to show in my visual as value1 and not just the ones for which I have data on those dates. To explain it better I want 1717 on the graph as well like the total in the table in the following image -

visual

Is there a way to do this in power BI? I tried to make a shared dimension of all unique key as a separate table and connecting both the tables to that table but there is no change in visual due to that

[Greg_Deckler]

@Anonymous - OK, I recreated this (PBIX file attached below sig). You want Table 3 and Table 4 and Page 14. So, first, I don't see the many-to-many, at least not in the sample avoid many-to-many if possible. Second, what is wrong is your table. It is exhibiting a measures total problem in effect. If you notice, your rows do not add up to the total of 1717.

You can deal with that here: This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

So, your stacked area chart is correct. 77 is the correct total for August and August includes 44 and 45 which in the other table add up to 497. Same story for July. 101 is the correct total for July and the other table for 44 and 45 add up to 497, still.

Next time, please paste sample data as table so I don't have to type so much! 🙂

Here are my standard New Member links, first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

( @Greg_Deckler Can you please not mark this reply as spam. My question is not answered and I am clarifying it if it is not clean)

Thank you very much for your reply. Sorry to post an image that I wasn't sure how to do the tables correctly.

Sorry if I wasn't very clear on my question, but I know that for those 2 keys the total is 497, but I still want it to show as 1717 on the graph as well as the table. I don't want the table to be corrected. I want the table logic of sum of value2 in my chart, the logic for value1 should remain same.

To give you an example use case, suppose that Table 1 represents the daily inventory in multiple warehouses (hence multiple entries for the same date) and Table 2 represents the destination inventory for those keys (materials). Table 2 has been calculated only in August this year and only the materials that are in use in August. Table 1 has historical data for many months and has materials that are being used now, but also some that were previously used but are not currently in use. Basically, old materials have been replaced by new material, but their goals are somewhat comparable.

But at the total level we can still make a comparison as total present vs target inventory. I want the same objective to show over the months and not base only the materials that are present on those dates on table 1.

I also want the destination to come dynamically from table 2 and not some fixed horizontal line as I want to drill down into the data based on the dimensions of other columns.

As for this is not many to many, you may consider an additional key in table 1 that is not present in table 2. Sorry I missed adding it in the sample representation of my table 1.

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Not very clear.

Is this your expected result?

You can check if the attached pbix file helps.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Thank you very much for your reply. Sorry to post an image that I wasn't sure how to do the tables correctly.

Sorry if I have not been very clear in my question, but I know that for those 2 keys the total is 497, but I still want it to show as 1717 in the graph as well as in the table. I don't want the table to be corrected. I want the table logic in my chart.

To give you an example use case, suppose that Table 1 represents the daily inventory in multiple warehouses (hence multiple entries for the same date) and Table 2 represents the destination inventory for those keys (materials). Table 2 has been calculated only in August of this year and only has the materials that are in use in august. Table 1 has historical data from many months and has materials that are being used now, but also some that were previously used but are not currently being used. Basically, old materials have been replaced by new material, but their goals are somewhat comparable.

But at the total level we can still make a comparison as total inventory present vs target. I want the same target to show across the months and not basis only the materials that are present on those dates in table 1.

I also want the destination to come dynamically from table 2 and not from some fixed horizontal line as I want to drill down the data based on the dimensions of other columns.

As for this is not many to many, you may consider an additional key in table 1 that is not present in table 2. Sorry I missed adding it in the sample representation of my table 1.

Edit - somehow my earlier reply was showing in Spanish so posting again

Edit 2- @Greg_Deckler have edited to make it more clear, earlier version was an auto-translated text from Spanish to English.